Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Update one rows based on previous row

I have this sample data

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

Open in new window


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
Avatar of lcohan
lcohan
Flag of Canada image

I believe something like code below should work however PLEASE do NOT run it directly against any live table - just take a copy of it and run it there to see/check the results and adjust if needed:

with cte_list (ranking,rowId,indicatorid,[year],calculatedvalue)
as 
(select top 1 ranking, rowid, indicatorid,[year],calculatedvalue from tabname with (nolock) 
	group by indicatorId,[year],calculatedvalue 
	having COUNT(*) > 1
)
 
update tabname
set ranking = (select ranking from cte_list where rowid = t.rowid)
from cte_list t 
where tabname.indicatorid = t.indicatorid and tabname.[year] = t.[year] and tabname.calculatedvalue = t.calculatedvalue;

Open in new window

Avatar of Camillia

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],indicatorid,rowid

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;

Open in new window


If I remove "rowId", then the "select top 1" gives a row but then how do I do the update?

group by calculatedvalue,[year],indicatorid
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'll try it.

>>Is it true that ALL rows (group by calculatedvalue,[year],indicatorid) MUST have the same ranking?
Let me ask.
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.