Forum Discussion
Latest Type for the every ID
I have a table like below. create table ##Test (id nvarchar(20), dat datetime, idType nvarchar(10)) insert into ##test values ('1001','2024-05-01', 'A') insert into ##test values ('1001','...
1. Merge the rows with the same id and idType into one row, and let MAX(dat) be its dat.
2. Partition all rows by id and sort the rows by dat DESC in each partition, also add a rowNumber column that increases from 1 in each partition.
3. Filter all rows with rowNumber less than or equal to 2, these will be the results.
Code is here
WITH CTE(id, dat, rowNumber, idtype)
AS
(
SELECT
id
, MAX(dat)
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY MAX(dat) DESC) AS rowNumber
, idType
FROM ##Test
GROUP BY
id, idType
)
SELECT id, idtype FROM CTE WHERE rowNumber<=2
- Thanks rodgerkong.. its working fine as expected.
