you can you
ROW_NUMBER()
function.
declare @tbl table
(
Id int,
Name nvarchar(50),
[STATUS] nvarchar(50),
[DateTime] date
)
INSERT INTO @tbl(Id,Name,[STATUS],[DateTime])
VALUES
(1, 'x', 'not valid', '2020-6-5'),
(2, 'y', 'valid', '2020-3-1'),
(1, 'x', 'valid', '2020-6-6')
declare @tbl2 table
(
Id int,
Name nvarchar(50),
[STATUS] nvarchar(50),
[DateTime] date,
RowNumber int
)
INSERT INTO @tbl2(Id,Name,[STATUS],[DateTime],RowNumber)
SELECT *,ROW_NUMBER() over( partition by Id order by [DateTime] desc) AS RowNumber FROM @tbl
SELECT * FROM @tbl2 WHERE [@tbl2].RowNumber=1
output :
Id Name STATUS DateTime RowNumber
1 x valid 2020-06-06 1
2 y valid 2020-03-01 1