Forum Discussion

kasimacsys's avatar
kasimacsys
Copper Contributor
Aug 27, 2024

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','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

 

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Aug 27, 2024

    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

     

    • kasimacsys's avatar
      kasimacsys
      Copper Contributor
      Aug 28, 2024
      Thanks rodgerkong.. its working fine as expected.
  • Javier_Villegas's avatar
    Javier_Villegas
    MVP
    Aug 27, 2024

    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;
    • kasimacsys's avatar
      kasimacsys
      Copper Contributor
      Aug 28, 2024
      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

Resources