February 1, 2008 at 2:11 pm
Guys,
I have scenario where I have to update flag field based on the following data combination.
Party_idcountseqflag (TO BE POPULATED)
____________________________________
21377711Y
21377721N
21377722Y
21377811N
21377812N
21377813Y
21377821N
The logic to populate flag = 'Y' is to pick up the max seq field for the same combination of PARTY_ID and COUNT.
Any suggestions and inputs would help
Thanks
February 1, 2008 at 4:21 pm
I played with this for a while, don't think I could do it without a unique column of some sort, unless I used a cursor and went row by row. So I bent the rules a little and added an identity column, hope you are able to get this to work...
create table table
(Party_id int,
count int,
seq int,
flag bit,
rowid int identity(1,1)
)
insert table values(213777,1,1,0)
insert table values(213777,2,1,0)
insert table values(213777,2,2,0)
insert table values(213778,1,1,0)
insert table values(213778,1,2,0)
insert table values(213778,1,3,0)
insert table values(213778,2,1,0)
update table
set flag = 1
where rowid in( select rowid from(
select t.*,row_number() over (partition by party_id,count order by seq desc)as num from table t
)as dt
where num = 1
)
February 1, 2008 at 4:40 pm
declare @sample table (Party_id int, count int, seq int, flag bit)
insert@sample (Party_id, count, seq)
select213777, 1, 1 union all
select213777, 2, 1 union all
select213777, 2, 2 union all
select213778, 1, 1 union all
select213778, 1, 2 union all
select213778, 1, 3 union all
select213778, 2, 1
UPDATEf
setf.flag = case when recid = 1 then 1 else 0 end
from(
selectflag,
rank() over (partition by party_id, count order by seq desc) AS recid
from@sample
) AS f
select*
from@sample
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply