I have table like follows

location   rank
location_A 1
location_B 2
location_C 3
location_D 4
location_E 5
location_F 6
・
・
・

And, closest location = location_E and second scond closest location =location_D so I would like to get following intermidiate table

location    rank
location_E  1
location_D  2

My desired result is as follows.location_E and location_D is moved its rank as 1 and 2 and remaining location preserve its order but slide its rank

location    rank
location_E  1
location_D  2
location_A  3
location_B  4
location_C  5
location_F  6
・
・

Are there any good way to achieve this? thanks

CodePudding user response：

Try the following using RANK function with conditional order by clause:

SELECT location,
       RANK() OVER (ORDER BY CASE 
                             WHEN location = 'location_E' THEN 1
                             WHEN location = 'location_D' THEN 2
                             ELSE 3
                             END, rank_) rank_
FROM table_name

See a demo.

CodePudding user response：

Longer, but it's an update you were after, plus it lets you just plug in your arbitrary list of values. If you were to use case, each time you'd have to reconstruct it based on incoming order, either manually or through dynamic SQL.

with 
 updates(location,rank) as
(values ('location_E',1),--add arbitrarily many, not having to modify the rest of the query
        ('location_D',2) )
,intermediate_ranks as 
  (  select location,
            -'infinity'::numeric as rank1,--puts new ranks always ahead of old
            rank as rank2
     from updates
     union 
     select location,
            'infinity'::numeric as rank1,--puts old ranks always behind the new
            rank as rank2
     from locations
     where location not in (select location from updates) )
,new_ranks as
  (  select location, 
             rank() over (order by rank1,rank2) as new_rank
     from intermediate_ranks)
update  locations l
set     rank=n.new_rank
from    new_ranks n
where   l.location=n.location;

Online demo

1. You could use an INSERT...ON CONFLICT DO UPDATE or MERGE in PostgreSQL15 to also handle adding new locations this way. Currently, you have to insert the new location first, then reorder them through a separate update.
2. '-infinity'::numeric and 'infinity'::numeric could be any pair of numbers placing new rank first and old rank second (e.g. 0 and 1) but it's fun to use a number that's by definition always lower/higher than any other.