Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
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 having an uproarious good time at the Monastery: (3)
As of 2018-09-26 01:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Eventually, "covfefe" will come to mean:













    Results (205 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!