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'
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
.