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

Question

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.

Answer 1

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