Row partition isuue

1

table

id,code,row,type
2,5,8,emp
2,4,9,emp
2,8,9,cus
2,8,10,cus

there are two distinct types emp, cus, if bothe exist i would like to set the primary flag based on the max row for each type. yu should never have the duplicate code

my expected output should be

id,code,row,type,primary
2,5,9,emp,1
2,8,10,cus,1

2

hi

hope this helps ..

please click arrow to the left for Sample Data Script 
drop table sample_data 
go 


create table sample_data 
(
id int ,code int ,row int ,type varchar(10)
)
go 

insert into sample_data select 2,5,8,'emp'
insert into sample_data select 2,4,9,'emp'
insert into sample_data select 2,8,9,'cus'
insert into sample_data select 2,8,10,'cus'
go

; with cte as 
(
select id,code,row,type,ROW_NUMBER() over(partition by id , type order by row desc ) as rn  from sample_data 
) 
select 'SQL Output', * from cte where rn = 1

image
image

1 Like