How to select non "unique" rows

Question

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

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)

What database are you using, and what version of that database are you using? — Mark Byers, Dec 23 '10 at 14:32
possible duplicate of [How to Select Every Row Where Column Value is NOT Distinct](http://stackoverflow.com/questions/13146304/how-to-select-every-row-where-column-value-is-not-distinct) — Bruno Martinez, Nov 26 '14 at 18:08
It works in MySQL, so I'm adding that tag. I wonder if OP is around to change it to the correct SQL dialect/version? — icedwater, Jun 23 '15 at 11:18

score 40 · Accepted Answer · answered Dec 23 '10 at 14:31

40

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 Dec 23 '10 at 14:31

Mark Byers

score 37 · Answer 2 · answered Dec 23 '10 at 14:29

37

Something like this should work:

SELECT idA, COUNT(*) FROM XXX GROUP BY idA HAVING COUNT(*) > 1

answered Dec 23 '10 at 14:29

judda

score 8 · Answer 3 · edited Apr 15 '19 at 16:03

8

SELECT id, idA, COUNT(infos) AS cnt
FROM mytable
GROUP BY infos
HAVING cnt > 1

edited Apr 15 '19 at 16:03

simhumileco

answered Dec 23 '10 at 14:43

Mchipouras

score 2 · Answer 4 · edited Apr 15 '19 at 16:02

2

This is probably what you are looking for:

SELECT *, COUNT(DISTINCT infos) FROM table GROUP BY idA HAVING COUNT(DISTINCT infos) > 1;

edited Apr 15 '19 at 16:02

simhumileco

answered Apr 17 '13 at 18:34

user2292112

It does not match "I'd like fetch all the rows for the column infos, that have a same idA value in at least two rows." – FreeLightman Dec 03 '15 at 01:16

Vincent Mimoun-Prat · Answer 5 · 2010-12-23T14:34:48.840

1

This should give all the rows where "infos" appear exactly once (Tested on MySQL)

SELECT id, idA, count(infos) AS cnt
FROM mytable
GROUP BY infos
HAVING cnt=1

Data

id  ida     infos
1   201     1955
2   202     1978
3   203     1978
4   204     1956
5   0   1980

Result

id  idA     cnt
1   201     1
4   204     1
5   0   1

edited Dec 23 '10 at 14:34

answered Dec 23 '10 at 14:29

1

This will not work on all databases, it will on MySQL, but not on MSSQL Server (the latter won't allow non-aggregated columns that are not in the group by in the select) – user254875486 Dec 23 '10 at 14:33

5 Answers5