CODEWITHSUNDEEP
sql-server
Dan Cundy
Dan Cundy

Reputation: 2849

Filter Duplicate records by closest data?

Background

I have multiple records in a table that some times have duplicate entries, apart from the data the record was created.

I have to pick between the duplicate records and change a field of the one with the latest date (last one to be created).

Currently I am doing this manually by visually checking the dates.

Question

Is here a way of only bring back one of the duplicates, the one with the closest day to today?

Example

Below is a query that brings back two sets of duplicates for one stationID. There should only be one record per assessment type. The isLive column would be changed to True for the bottom two record as they have the latest Filedate records.

SQL

SELECT StationFileID
      ,StationID
      ,AssessmentType
      ,URL
      ,FileDate
      ,isLive
      ,StationObjectID
FROM StationFiles
WHERE StationID = '1066'
ORDER BY StationID;

Records Returned

enter image description here

Upvotes: 0

Views: 70

Answers (1)

dario
dario

Reputation: 5269

You can use the ROW_NUMBER() function to identify the latest rows:

SELECT *
      ,CASE WHEN N = 1 THEN 'True'
                       ELSE 'False' END AS isLive
FROM (SELECT StationFileID
            ,StationID
            ,AssessmentType
            ,FileDate
            ,ROW_NUMBER() OVER (PARTITION BY StationID, AssessmentType ORDER BY FileDate DESC) AS N
FROM StationFiles
WHERE StationID = '1066') AS T

Upvotes: 2

Related Questions