postgresqlplsqlpgadminrankranking-functions

How to use Rank Function With Condition in Postgres


For the following sample table I want to set ranks to each group order by start_date desc but I want the admin to be always set to rank 0

name start_Date group_no
Admin 2023-01-01 0
A 2021-01-01 0
B 2022-01-01 0
C 2021-06-01 0
Admin 2021-01-01 1
A 2025-01-01 1
B 2020-01-01 1
C 2024-01-01 1

Expected result:

name start_Date group_no rnk
Admin 2023-01-01 0 0
B 2022-01-01 0 1
C 2021-06-01 0 2
A 2021-01-01 0 3
Admin 2021-01-01 1 0
A 2025-01-01 1 1
C 2024-01-01 1 2
B 2020-01-01 1 3
SELECT name, start_date, group_no, 
RANK() OVER (PARTITION BY group_no ORDER by start_date DESC) as rnk
FROM Table_Name
ORDER BY rnk;

The above query gives me the following result. Is there a way I can add a condition on rank function to set admin to 0 by default and apply rank function to everything else.

name start_Date group_no rnk
Admin 2023-01-01 0 1
B 2022-01-01 0 2
C 2021-06-01 0 3
A 2021-01-01 0 4
A 2025-01-01 1 1
C 2024-01-01 1 2
Admin 2021-01-01 1 3
B 2020-01-01 1 4

I have also tried using case condition:

SELECT name, start_date, group_no, 
CASE WHEN name = 'Admin' THEN 0 
ELSE RANK() OVER (PARTITION BY group_no ORDER by start_date DESC) 
END as rnk
FROM Table_Name
ORDER BY rnk;
name start_Date group_no rnk
Admin 2023-01-01 0 0
B 2022-01-01 0 2
C 2021-06-01 0 3
A 2021-01-01 0 4

In this instance, Admin gets set to 0 but for the subsequent rows the ranking starts from 2 instead of 1.

The other option that I have is to use the following which does the job but is there a better approach to this?

SELECT name, start_date, group_no, 
0 as rnk
FROM Table_Name
WHERE name = 'Admin'
UNION 
SELECT name, start_date, group_no, 
RANK() OVER (PARTITION BY group_no ORDER by start_date DESC) as rnk
FROM Table_Name
WHERE name <> 'Admin'

Solution

  • The following query produces the OP's expected results:

    SELECT
      name,
      start_date,
      group_no,
      DENSE_RANK()
        OVER (PARTITION BY group_no
              ORDER BY CASE WHEN name <> 'Admin' THEN start_date END DESC)
      - BOOL_OR(name = 'Admin') OVER (PARTITION BY group_no)::INTEGER AS rnk
    FROM
      table_name;
    

    DENSE_RANK is used instead of RANK so that multiple occurrences of name = 'Admin' or of start_date don't introduce gaps in the rankings. The result is that rnk represents recency within a group with rows where name = 'Admin' always considered the most recent regardless of date. Note that this query will rank NULL start_date as more recent than valued start_date.

    The following version removes the second window function (as suggested by @ysth):

    SELECT
      name,
      start_date,
      group_no,
      CASE WHEN name = 'Admin' THEN 0
           ELSE DENSE_RANK()
                  OVER (PARTITION BY group_no
                  ORDER BY name IS NOT DISTINCT FROM 'Admin', start_date DESC NULLS LAST) END AS rnk
    FROM
      table_name
    ORDER BY group_no, rnk, name;
    

    Note that this version ranks NULL start_date as less recent than valued start_date.