[SOLVED] How to select non "unique" rows

Issue

I have the following table, from which i have to fetch non unique rows

id idA infos
0 201 1899
1 205 1955
2 207 1955
3 201 1959

I’d like fetch all the rows for the column infos, that have a same idA value in at least two rows.

Output of the query for the above table must be

infos
1899
1959 

I’ve tried the following requests with no success :

  • SELECT idA FROM XXX WHERE NOT EXISTS(SELECT * FROM XXX GROUP BY idA)
  • SELECT * FROM XXX a WHERE NOT EXISTS(SELECT * FROM XXX b WHERE a.RVT_ID=b.RVT_ID GROUP BY idA)

Solution

Try this:

SELECT T1.idA, T1.infos
FROM XXX T1
JOIN
(
    SELECT idA
    FROM XXX
    GROUP BY idA
    HAVING COUNT(*) >= 2
) T2
ON T1.idA = T2.idA

The result for the data you posted:

idaA  infos
201   1899
201   1959

Answered By – Mark Byers

Answer Checked By – Gilberto Lyons (BugsFixing Admin)

Leave a Reply

Your email address will not be published.