Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Select all duplicates from SQLite

by IB2017 (Scribe)
on Sep 12, 2018 at 22:07 UTC ( #1222260=perlquestion: print w/replies, xml ) Need Help??
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");

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

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?

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
    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

                    Memory fault   --   brain fried

      Thank you. Perfect.

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( ... );

    Then to access it, do something like:

    while ( my $duplicates = pop(@alldups) ) { whatever... }
    David Lee Crites
    lee@critesclan.com

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1222260]
Approved by Discipulus
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (10)
As of 2018-11-12 22:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My code is most likely broken because:
















    Results (146 votes). Check out past polls.

    Notices?