Select Where Count() of multiple columns is greater than one

Question

Say I have data like this:

ClaimID ClaimLine    Date
   1        1     22/06/2016
   1        1     01/01/2016
   2        1     08/06/2016
   2        2     31/01/2015
   3        1     23/03/2013

How would I select the duplicate ClaimIDs and ClaimLine with the lowest date?

So the return value would be

1,1,01/01/2016

Answer 1

You can group by the columns you want to deduplicate, and select the minimum date from each group:

SELECT ClaimID, ClaimLine, MIN(Date)
FROM Table
GROUP BY ClaimID, ClaimLine
ORDER BY ClaimID, ClaimLine

If you further only want to see the ClaimIDs and ClaimLines that have been duplicated, you can add a HAVING clause:

SELECT ClaimID, ClaimLine, MIN(Date)
FROM Table
GROUP BY ClaimID, ClaimLine
HAVING COUNT(*) > 1
ORDER BY ClaimID, ClaimLine