Question 113762 in PostgreSQL Question posted in
The official documentation can be found here.

How to slide rank and preserve its order in postgresql

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

Tags:

2

Answers


  1. 0

    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

    And if you want to update your table with the new ranks, try the following:

    WITH ranks AS
(
  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
)

UPDATE table_name T
SET rank_ = R.rank_
FROM ranks R
WHERE T.location = R.location;

    See a demo.

    Login or Signup to reply.
  2. 0

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search