Select all duplicates from SQLite

IB2017 has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks

I want to select all duplicates from my database. I am using the following:

my $duplicates= $dbh->selectall_arrayref("SELECT ID, Tag1, Tag2, COUNT
+(*) c FROM table GROUP BY Tag1, Tag2 HAVING c > 1");
[download]

This identifies if two (or more) entries are duplicates (Tag1 and Tag2 in combination), however it retrieves only the first (?) occurrence, not all duplicated entries. If I have:

1:science:math
2:science:algebra
3:history:math
4:science:math
[download]

The select statement returns 1:science:math, not 4:science:math. Is there a way to change it in order to have in my array all duplicates?

Comment on Select all duplicates from SQLite Select or Download Code

Replies are listed 'Best First'.
Re: Select all duplicates from SQLite by NetWallah (Canon) on Sep 12, 2018 at 22:35 UTC
This is a SQL question, not perl Anyway see : https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table. Your query will have to be something like: `SELECT y.id,y.name,y.email FROM @YourTable y INNER JOIN (SELECT name,email, COUNT() AS CountOf FROM @YourTable GROUP BY name,email HAVING COUNT()>1 ) dt ON y.name=dt.name AND y.email=dt.email` [download] UPDATE: The following schema/query gets the desired result: `sqlite> CREATE TABLE "table"(ID,Tag1,Tag2); -- Populate table, then" select * from "table"; ID Tag1 Tag2 ---------- ---------- ---------- 1 science math 2 science algebra 3 history math 4 science math sqlite> SELECT t.id,t.tag1,t.tag2 from "table" t inner join ( SELECT Tag1, Tag2, COUNT() c FROM 'table' GROUP BY Tag1, Tag2 HAVING c > 1) x on t.tag1=x.tag1 and t.tag2=x.tag2; ID Tag1 Tag2 ---------- ---------- ---------- 1 science math 4 science math` [download] Memory fault -- brain fried*	[reply] [d/l] [select]
Re^2: Select all duplicates from SQLite by IB2017 (Pilgrim) on Sep 13, 2018 at 08:31 UTC
Thank you. Perfect.	[reply]
Re: Select all duplicates from SQLite by lee_crites (Scribe) on Sep 14, 2018 at 04:17 UTC
Since you are supplying it with a scalar ($duplicates), it only returns the first one. If you want them all, change the code to something like: `my @alldups = $dbh->selectall_arrayref( ... );` [download] Then to access it, do something like: `while ( my $duplicates = pop(@alldups) ) { whatever... }` [download] David Lee Crites lee@critesclan.com	[reply] [d/l] [select]