Select records where a combinations of columns are repeated using SQLite

Question

I have a table with the following columns:

a|b|c|d|e

and I want to select all the records such that a combination of a subset of the columns is repeated (say, a, b, c). Example:

a|b|c|d|e
1|2|3|1|2
6|7|8|9|1
1|2|3|4|5
1|2|2|5|4

should result in:

1|2|3|1|2
1|2|3|4|5

Answer 1

This will do it:

SELECT a,b,c,d,e
  FROM t t1
 WHERE EXISTS ( SELECT NULL
                  FROM t t2
                 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
                   AND t1.rowid <> t2.rowid
              )

A second way (SQLite does not support (x,y) in (select two columns)):

SELECT a,b,c,d,e
  FROM t
 WHERE quote(a)||','||quote(b)||','||quote(c)
    IN ( SELECT quote(a)||','||quote(b)||','||quote(c)
           FROM t
          GROUP BY a,b,c
         HAVING COUNT(*) > 1
       )

A third way:

SELECT a,b,c,d,e
  FROM ( SELECT a,b,c
           FROM t
          GROUP BY a,b,c
         HAVING COUNT(*) > 1
       )
NATURAL JOIN t

This last one has an advantage, you can also add aggregate rows (like if you used analytic functions in other RDBMS:)

sqlite> .headers on
sqlite> .mode column
sqlite> .width 3 3 3 3 3 20
sqlite> SELECT a,b,c,d,e,"Rows in this group"
   ...>   FROM ( SELECT a,b,c
   ...>               , COUNT(*) AS "Rows in this group"
   ...>            FROM t
   ...>           GROUP BY a,b,c
   ...>          HAVING COUNT(*) > 1
   ...>        )
   ...> NATURAL JOIN t;
a    b    c    d    e    Rows in this group
---  ---  ---  ---  ---  --------------------
1    2    3    1    2    2
1    2    3    4    5    2

which is similar to this in Oracle:

   WITH x AS
      (SELECT a,b,c,d,e,COUNT(*) OVER (PARTITION BY a,b,c) "Rows in this group"
         FROM t
      )
   SELECT * FROM x WHERE "Rows in this group" >= 2