Logo Questions Linux Laravel Mysql Ubuntu Git Menu

SQL Select distinct rows with duplicate values in one column and choose one row for each duplicate based on value in primary key field

I have an SQL table with duplicate records on FacilityID. FacilityKey is unique. I need to select rows that have duplicates on FacilityID but I only want to show one record for each and I want to choose the one with the most recent (highest) FacilityKey. Can anybody help me figure out how to write my query? I've tried everything I could think of and searched the internet for something similar to no avail. All I can find are examples of identifying the duplicate records.

like image 268
BrendaC Avatar asked Mar 17 '23 16:03


1 Answers

Something like this should work:

select FacilityID, max(FacilityKey)
from Facilities
group by FacilityID
having count(FacilityID)>1

And then if you want to get all of the fields, something like this:

select *
from facilities
    inner join (
        select FacilityID, max(FacilityKey) as maxkey
        from Facilities
        group by FacilityID
        having count(FacilityID)>1
      ) t on t.FacilityID = facilities.FacilityID and t.maxkey=facilities.FacilityKey
like image 101
Seamus Avatar answered Mar 19 '23 04:03