CODEWITHSUNDEEP
sqlsql-server
SveGeorgiev
SveGeorgiev

Reputation: 31

sql - query which extracts all records but only the most recent record of a duplicate

What query I need which extracts all records but only the most recent record of a duplicate?

I have this query for records of duplicates:

    $SELECT DISTINCT * 
FROM ['Duplicate Data$'] t1
WHERE Name in
(
    SELECT Name
    FROM ['Duplicate Data$']
    GROUP BY Name
    HAVING COUNT(*)>1
)
ORDER BY Name$

And this for most recent record: 

$SELECT DISTINCT *
FROM  ['Duplicate Data$'] t1
WHERE Date = (SELECT max(Date) FROM ['Duplicate Data$'] WHERE t1.Name =['Duplicate Data$'].Name)
ORDER BY Date DESC$

Upvotes: 1

Views: 144

Answers (1)

T McKeown
T McKeown

Reputation: 12847

SELECT DISTINCT T1.* 
FROM ['Duplicate Data$'] t1
JOIN (
  SELECT Name, MAX(DATE) AS [MaxDate]
  FROM ['Duplicate Data$']
  GROUP BY Name
  HAVING COUNT(*)>1
) AS D
ON D.Name = T1.Name 
AND D.MaxDate = T1.Date
ORDER BY T1.Name

Upvotes: 2

Related Questions