Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Select all duplicates from SQLite

by IB2017 (Monk)
on Sep 12, 2018 at 18: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 18:35 UTC
    This is a SQL question, not perl

    Anyway see :

    Your query will have to be something like:

    SELECT,, FROM @YourTable y INNER JOIN (SELECT name,email, COUNT(*) AS CountOf FROM @YourTable GROUP BY name,email HAVING COUNT(*)>1 ) dt ON AND
    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.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 00: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

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2019-01-20 07:37 GMT
Find Nodes?
    Voting Booth?
    After Perl5, I'm mostly interested in:

    Results (345 votes). Check out past polls.

    • (Sep 10, 2018 at 18:53 UTC) Welcome new users!