Duplicate Records

[phanpa]

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

[#2]

That's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.

Olaf

[#3 · Accepted solution]

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