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

Postgresql – How to use SQL dense_rank and use original indexing

I am trying to generate a query with an ordered ranking of duplicates. For example if I have the following table

id | col1 | col2 | col3
---+------+------+------
1  |  3   |  a   |  x 
2  |  2   |  a   |  y 
3  |  1   |  b   |  y
4  |  2   |  c   |  z 
5  |  3   |  a   |  x 
6  |  3   |  a   |  x
7  |  2   |  a   |  y

My desired output would be:

id | col1 | col2 | col3 | rank
---+------+------+------+-----
1  |  3   |  a   |  x   |  1
2  |  2   |  a   |  y   |  2
3  |  1   |  b   |  y   |  3
4  |  2   |  c   |  z   |  4
5  |  3   |  a   |  x   |  1
6  |  3   |  a   |  x   |  1
7  |  2   |  a   |  y   |  2

Where the all rows that are equal across the columns are similarly ranked and the ranking starts from 1 in the original order.

I have tried

SELECT
      id
    , DENSE_RANK() OVER (ORDER BY col1, col2, col2) AS rank
FROM
    test_table;

but what I get is

id | col1 | col2 | col3 | rank
---+------+------+------+-----
1  |  3   |  a   |  x   |  4
2  |  2   |  a   |  y   |  2
3  |  1   |  b   |  y   |  1
4  |  2   |  c   |  z   |  3
5  |  3   |  a   |  x   |  4
6  |  3   |  a   |  x   |  4
7  |  2   |  a   |  y   |  2

Where the rank count is based off the ORDER BY in the query and I can’t figure out how to change this.

Tags:

2

Answers


  1. 0

    As far as I know there is no built in way to do this, but you can use a sub query to achieve your result

    SELECT temp.*, DENSE_RANK() OVER (ORDER BY rank_id)
FROM ( 
  SELECT col1, col2, col3, min(id) AS rank_id 
  FROM temp
  GROUP BY col1, col2, col3 
) pg_sucks JOIN temp USING (col1, col2, col3)
ORDER BY id;

    Here is what I did (ignore the bad names, its 23:30 here if it makes it more redeemable)

    The sub query finds all the present col1/2/3 combinations and assigns them their lowest ranked id, from there we link those columns with the outer query ones and we do a dense_rank sort based on the rank_id, oh and also sort them by id so we get them in order.

    Feel free to play with it here

    Also as a footnote you dont need to give a table allias since pgSQL-16, so if you are running that omit pg_sucks

    Login or Signup to reply.
  2. 0

    You only need to change to ORDER BY of the DENSE_RANK:

    SELECT
      id, col1, col2, col3
    , DENSE_RANK() OVER (ORDER BY col2, col1 desc, col2) AS rank
FROM
    test_table
ORDER BY id
  ;

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search