Forum Discussion
Duplicate Records
Hi,
How do a write a view in SQL Server that pulls the most updated record so there's no duplicate id?
Example:
id status date
123 pending 2022-02-25
123 completed 2022-06-15
257 pending 2021-04-09
345 completed 2020-09-03
- As Olaf said, a little more Info would be helpful.
Anyway, assuming your date is actually a datetime, what you could do:
SELECT a.* FROM [table] a
INNER JOIN
(SELECT id,max(date) [date] from [table] group by id) b
ON a.id = b.id and a.[date] = b.[date]
There are multiple other ways depending on the actual data structure.
Ben
- As Olaf said, a little more Info would be helpful.
Anyway, assuming your date is actually a datetime, what you could do:
SELECT a.* FROM [table] a
INNER JOIN
(SELECT id,max(date) [date] from [table] group by id) b
ON a.id = b.id and a.[date] = b.[date]
There are multiple other ways depending on the actual data structure.
Ben - That's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.
Olaf
