# Row over partition scenario

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

``````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``````

I have a question

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

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...

hi

hope this helps

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
``````