Andrew Kilburn Andrew Kilburn - 5 months ago 6
MySQL Question

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

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

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
Comments