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','...
Hello kasimacsys , you can do this with a CTE
;WITH CTE AS (
SELECT
id,
idType,
dat,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY dat DESC) AS rn
FROM
##Test
GROUP BY
id, idType, dat
)
SELECT
id,
idType,
'insert into ##result values ('''+CAST(id AS VARCHAR(20))+''','''+idType+''')'
FROM
CTE
WHERE
rn <= 2
ORDER BY
id, dat DESC;
- thanks javier for your reply.. its should be the latest distinct id type.. i have pasted the expected output. anyway rodgerkong response working fine.. thanks again
