SQL Select distinct rows with duplicate values in one column and choose one row for each duplicate based on value in primary key field
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
BrendaC
I am a programmer for the State of Illinois. I have been programming since 2000. The majority of my experience was in COBOL, using VSAM processing but have switched to programming in Visual Studios, vb.net using databases in SQL server for a few years now.
Updated on June 04, 2022Comments
-
BrendaC almost 2 years
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.