CODEWITHSUNDEEP
mysqlsql
AdrienG
AdrienG

Reputation: 273

How to select non "unique" rows

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 :

Upvotes: 27

Views: 63201

Answers (5)

Mchipouras
Mchipouras

Reputation: 81

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

Upvotes: 8

user2292112
user2292112

Reputation: 21

This is probably what you are looking for:

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

Upvotes: 2

Vincent Mimoun-Prat
Vincent Mimoun-Prat

Reputation: 28541

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838376

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

Upvotes: 40

judda
judda

Reputation: 3972

Something like this should work:

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

Upvotes: 36

Related Questions