Getting rows with duplicate column values

Question

I tried this with solutions avaialble online, but none worked for me.

Table :
Id rank 
1  100
1  100
2  75
2  45
3  50
3 50

I want Ids 1 and 3 returned, beacuse they have duplicates.

I tried something like

 select * from A where rank in ( 
 select rank from A group by rank having count(rank) > 1

This also returned ids without any duplicates. Please help.

http://stackoverflow.com/questions/4010311/how-to-check-if-all-fields-are-unique-in-oracle — tyjkenn, Feb 28 '12 at 02:58
Duplicate question, the irony! - http://stackoverflow.com/questions/2053569/is-it-possible-for-sql-to-find-records-with-duplicates — J Cooper, Feb 28 '12 at 03:10
@JCooper: Sorry. I looked up online before posting. Tried couple. But didnt know I need to group by both id and rank. — user393148, Feb 28 '12 at 03:16

score 4 · Accepted Answer · answered Feb 28 '12 at 02:56

4

Try this:

select id from table
group by id, rank
having count(*) > 1

answered Feb 28 '12 at 02:56

Mosty Mostacho

This gives me Id with more than one value. I am trying to get the ones with duplicates. Thanks – user393148 Feb 28 '12 at 03:00
Yeap sorry, correected it. I missed the rank – Mosty Mostacho Feb 28 '12 at 03:00
Thank you. :) I dont know what I would do without these forums and you guys. Learning each time..:) – user393148 Feb 28 '12 at 03:03
1

Welcome ^.^ Anyway, this is not a forum -- The main difference is that all the interaction here is asking a question, answering the question, voting it and accepting answers. Nothing else :) – Mosty Mostacho Feb 28 '12 at 03:09

score 1 · Answer 2 · answered Feb 28 '12 at 03:01

1

select id, rank
from
(
    select id, rank, count(*) cnt
    from rank_tab
    group by id, rank
    having count(*) > 1
) t

answered Feb 28 '12 at 03:01

Thit Lwin Oo

score 1 · Answer 3 · answered Feb 28 '12 at 03:13

This general idea should work:

SELECT id
FROM your_table
GROUP BY id
HAVING COUNT(*) > 1 AND COUNT(DISTINCT rank) = 1

In plain English: get every id that exists in multiple rows, but all these rows have the same value in rank.

If you want ids that have some duplicated ranks (but not necessarily all), something like this should work:

SELECT id
FROM your_table
GROUP BY id
HAVING COUNT(*) > COUNT(DISTINCT rank)

3 Answers3