Camillia
asked on
Update one rows based on previous row
I have this sample data
1. You see the "ranking" column is sequential now
2. You see "ranking" 4 and 5....they both have the same calculatedvalue of 171.66
This is what I want to do: if 2 rows with the same "indicatorId" and "year" have the same "calculatedvalue", the ranking should be the same...in this case, both rows should have 4 as ranking
How can I update the table to do this?
(I know I can write a while-loop and have variables and loop thru each row and do an update but I have 600,000 rows of data) Any other way of doing this?
This is SQL 2014
create table #main
(
rowId int identity,
indicatorid int,
[year] int,
countyid int,
calculatedvalue decimal(10,2),
ranking int
)
insert into #main
select 2,1991,3,175.00,1
insert into #main
select 2,1991,3,185.10,2
insert into #main
select 2,1991,3,193.66,3
insert into #main
select 2,1991,3,171.66,4
insert into #main
select 2,1991,3,171.66,5
insert into #main
select 2,1991,3,200.66,6
insert into #main
select 2,1991,3,88.66,7
insert into #main
select 3,2001,5,877.66,8
insert into #main
select 3,1991,5,322.66, 9
select * from #main
1. You see the "ranking" column is sequential now
2. You see "ranking" 4 and 5....they both have the same calculatedvalue of 171.66
This is what I want to do: if 2 rows with the same "indicatorId" and "year" have the same "calculatedvalue", the ranking should be the same...in this case, both rows should have 4 as ranking
How can I update the table to do this?
(I know I can write a while-loop and have variables and loop thru each row and do an update but I have 600,000 rows of data) Any other way of doing this?
This is SQL 2014
ASKER
No, this won't work. "rowid" is not unique ..it's an identity field so this won't give any values when I have
group by calculatedvalue,[year],ind icatorid,r owid
If I remove "rowId", then the "select top 1" gives a row but then how do I do the update?
group by calculatedvalue,[year],ind icatorid
group by calculatedvalue,[year],ind
with cte_list (ranking,rowId,indicatorid,[year],calculatedvalue)
as
(
select top 1 calculatedvalue,[year],indicatorid,rowid from #main with (nolock)
group by calculatedvalue,[year],indicatorid,rowid
having COUNT(*) > 1
)
update #main
set ranking = (select ranking from cte_list where rowid = t.rowid)
from cte_list t
where #main.indicatorid = t.indicatorid and #main.[year] = t.[year] and #main.calculatedvalue = t.calculatedvalue;
If I remove "rowId", then the "select top 1" gives a row but then how do I do the update?
group by calculatedvalue,[year],ind
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll try it.
>>Is it true that ALL rows (group by calculatedvalue,[year],indLet me ask.icatorid) MUST have the same ranking?
ASKER
worked on my sample data above. But not on the actual data. I'll take it from here and open a new question if i dont figure it out.
thanks for the help.
thanks for the help.
Open in new window