Latest Type for the every ID

[kasimacsys]

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','2024-05-02', 'B')
insert into ##test values ('1001','2024-05-03', 'C')
insert into ##test values ('1001','2024-05-04', 'C')

insert into ##test values ('1002','2024-05-01', 'A')

insert into ##test values ('1003','2024-05-01', 'B')
insert into ##test values ('1003','2024-05-02', 'C')
insert into ##test values ('1003','2024-05-03', 'A')

select * from ##Test
drop table ##test

i need  latest distinct last two idType against every ID

I need the result like below

create table ##result (id nvarchar(20), result nvarchar(10))
insert into ##result values ('1001','C')
insert into ##result values ('1001','B')
insert into ##result values ('1002','A')
insert into ##result values ('1003','A')
insert into ##result values ('1003','C')

select * from ##result
drop table ##result

[#2]

@kasimacsys 

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

[#3]

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;

[#4]

Thanks rodgerkong.. its working fine as expected.

[#5]

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