Tony Sepia Tony Sepia - 26 days ago 12
SQL Question

SQL - return extra columns based on group and count results

Source data:

+-----+-------------+--------+---------+
| ID | CandidateID | Rating | Name |
+-----+-------------+--------+---------+
| 777 | 119 | 5 | Fred |
| 777 | 120 | 5 | Tony |
| 777 | 121 | 3 | Ben |
| 888 | 131 | 4 | Joe |
| 888 | 132 | 4 | Matt |
| 888 | 133 | 1 | Russell |
+-----+-------------+--------+---------+


I need to find duplicates (where ID and Rating are the same), but also somehow keep a reference to them (CandidateID) to present their names in the resulting table.
Desired output (only shows rows where ID AND Rating are the same):

+-----+-------------+--------+------+
| ID | CandidateID | Rating | Name |
+-----+-------------+--------+------+
| 777 | 119 | 5 | Fred |
| 777 | 120 | 5 | Tony |
| 888 | 131 | 4 | Joe |
| 888 | 132 | 4 | Matt |
+-----+-------------+--------+------+


My initial approach was to GROUP by ID and Rating, producing COUNT, then do HAVING COUNT(*) >= 2, and then listing all rows where ID from that result is present. Sadly, that also returns non-duplicate rows. Is there a better solution?

Answer

One simple way uses exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.id = t.id and t2.rating = t.rating and
                    t2.candidateid <> t.candidateid
             );
Comments