Row over partition scenario

1

Whenever a id has duplicate code bring back max rows, then set primary flag to 1

Table
Id,code,row_id,flag
2,0,5,-1
2,0,6,null
2,18,8,1
2,18,9,1
2,7,1,1

Results should be

Select id, code,row_id,flag,case when row_num =1 then 1 else 0 end as primary
From
( select id,code,row_id,flag, row number() over (partition by Id, prefer by row_id des) as row_num

From table
)c

The result should be
Id,code,row_id,flag,primary
2,0,6,null,0
2,18,9,1,1
2,7,1,1,0

2 
create table #t (id int, Code int, Rows int, Flag int)

insert into #t values 
 (2,0,5,-1   )
,(2,0,6,null )
,(2,18,8,1	 )
,(2,18,9,1	 )
,(2,7,1,1	 )


select ID, Code, Rows, Flag from (
select ID, Code, Rows, Flag,
		ROW_NUMBER() over (partition by id, code order by id, code, rows desc) as Rownum
  from #t) v
where rownum = 1
3

I have a question

4

Your result returns all 1 for primary I only want the highest rows as primary and the others 0

Expected output
If,code,rows,flag
2,0,6,0
2,7,1,0
2,18,9,1. —is the max rows should be set to one

Thx again

5

This doesn't make sense - you start with 5 rows and end up with only 3 rows? How is that selection made and why?

Then - you want to flag the 'max' row - but it isn't clear what defines the 'max' row.

You could use 2 row numbers...one to identify the current row by Id, Code - and a second row number to identify rows by Id. But it isn't clear that is what you are asking...

6

hi

hope this helps :slight_smile:

please click arrow to the left for Sample Data

Sample Data Script 
drop table #sampledata
go 

create table #sampledata
(
Id int ,
code int,
row_id int,
flag int 
)
go 


insert into #sampledata select 2,0,5,-1
insert into #sampledata select 2,0,6,null
insert into #sampledata select 2,18,8,1
insert into #sampledata select 2,18,9,1
insert into #sampledata select 2,7,1,1

; with cte as 
(
select id,code,max(row_id) maxrowid from #sampledata 
group by id , code
)
select  'SQL Output', a.id,a.code,a.row_id,case when b.maxrowid is null then 0 else 1 end as flag 
from #sampledata a 
left join cte b 
on a.row_id = maxrowid and a.id = b.id and a.code = b.code 

go

image