Finding the most recent records from duplicates

userFs91 0
Viewed 31

I'm trying to get the most recent records from a table where there are duplicates for each row. Every month a new row for some IDs is getting added to the table, but some other records might not have a new row each month so the data will be like this

ID   Date
1    8/30/2022
1    7/30/2022
3    8/30/2022
3    7/30/2022
3    6/30/2022 
4    1/11/2021

The query result should be

ID   Date
1    8/30/2022
3    8/30/2022
4    1/11/2021

I tried to use a sub-query, but it is only returning records that actually has the most recent for the whole table not per ID so it is only returning those who has a record in 8/30/2022.

This is my query

create table  test as (
select * from table1 inner join
(select EmpID, max(Record_Date) as maxdate
from table1 group by EmpID) ms
on table1.EmpID ms.EmpID and Record_Date=maxdate)
 WITH DATA;
teradata sql
1 Answers

You may use NOT EXISTS operator with correlated subquery as the following:

SELECT T.ID, T.Date
FROM Table1 T
WHERE NOT EXISTS(SELECT * FROM Table1 D WHERE D.ID=T.ID AND D.Date>T.Date)

And of course, if you want to create a new table from this statement the query will be:

CREATE TABLE test AS 
  (
    SELECT T.ID, T.Date
    FROM Table1 T
    WHERE NOT EXISTS(SELECT * FROM Table1 D WHERE D.ID=T.ID AND D.Date>T.Date)
  ) WITH DATA;
userGpjR0
Related