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.

Duplicate question, the irony! - stackoverflow.com/questions/2053569/… — J Cooper
– J Cooper, Commented Feb 28, 2012 at 3:10
@JCooper: Sorry. I looked up online before posting. Tried couple. But didnt know I need to group by both id and rank. — user393148
– user393148, Commented Feb 28, 2012 at 3:16

Mosty Mostacho · Accepted Answer · 2012-02-28 02:56:41Z

4

Try this:

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

answered Feb 28, 2012 at 2:56

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user393148 Over a year ago

This gives me Id with more than one value. I am trying to get the ones with duplicates. Thanks

Mosty Mostacho Over a year ago

Yeap sorry, correected it. I missed the rank

user393148 Over a year ago

Thank you. :) I dont know what I would do without these forums and you guys. Learning each time..:)

Mosty Mostacho Over a year ago

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 :)

Thit Lwin Oo · Accepted Answer · 2012-02-28 03:01:06Z

1

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

answered Feb 28, 2012 at 3:01

Thit Lwin Oo

3,4283 gold badges22 silver badges23 bronze badges

Comments

Branko Dimitrijevic · Accepted Answer · 2012-02-28 03:13:14Z

1

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)

answered Feb 28, 2012 at 3:13

Branko Dimitrijevic

52.3k12 gold badges104 silver badges174 bronze badges

Collectives™ on Stack Overflow

Getting rows with duplicate column values

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related