Reordering data in a table
If I have this data in a table in MSSQL, which is a horizontal row of objects and their coordinates:
row id value
35395 776130 0
35395 776131 20
35395 776132 40
35395 776133 60
35395 776134 80
35395 776135 100
35395 776136 120
35395 776137 140
35395 776138 160
35395 776139 180
35395 776140 200
And I want to change the values so that they are ordered in descending order (flip the line 180 degrees):
row id value
35395 776130 200
35395 776131 180
35395 776132 160
35395 776133 140
35395 776134 120
35395 776135 100
35395 776136 80
35395 776137 60
35395 776138 40
35395 776139 20
35395 776140 0
How can I do this with the update instruction without doing it one by one?
source to share
Using Seb's answer, I formulated a solution:
declare @maxcoord int;
declare @rowid int; set @rowid = 35395
select @maxcoord = max(value) from table where row = @rowid
update table set value = @maxcoord - value
where row = @rowid
I think I'll create a stored procedure for this as I don't think it will be the only time it will be used :)
source to share
I do not know the nature of the problem, but just based on your example, can you please update all the lines doing
UPDATE `thetable` SET value = 200 - value
Again, I don't know the nature of the problem, so it might not be that easy, but again this seems to work in your simplified example and might work in your real problem as well.
source to share
If you are using SQL Server 2005 or later, this might do it for you. The advantage is that this is the only request.
with T1(rn,row,id,value) as (
select
rank() over (
order by value,row,id
),
row,id,value
from T
), T2(rn,value) as (
select
row_number() over (
order by value desc
), value
from T
), T12(row,id,value,vnew) as (
select row,id,T1.value,T2.value
from T1 join T2 on T1.rn = T2.rn
)
update T12 set
value = vnew;
I assumed that the value column was initially increasing and you want it to decrease, but I assumed that two consecutive values would be the same as long as (row, id) is the key for the table.
There are other possibilities as well. For example, if (string, id) is a key and you want to change the order of the "value" values from whatever position they are in to the "mirror" position, you can do this:
with T1(rn,row,id,value) as (
select
rank() over (
order by row,id
),
row,id,value
from T
), T2(rn,value) as (
select
rank() over (
order by row desc,id desc
), value
from T
), T12(row,id,value,vnew) as (
select row,id,T1.value,T2.value
from T1 join T2 on T1.rn = T2.rn
)
update T12 set
value = vnew;
With the right indices, this can be relatively efficient.
source to share