By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
Create Table table_name(priority1 VARCHAR(50), priority2 VARCHAR(50), priority3 VARCHAR(50));
Insert Into table_name Values
('x','a','n'),
('g',null,null),
(null,null,null),
('r',null,'i'),
('a','a',null),
(null,'y','a'),
(null,null,'t'),
(null,'w',null);
8 rows affected
Select priority1, priority2, priority3,
DENSE_RANK() Over (Order By Case When priority1 IS NOT null Then 1 End,
Case When priority2 IS NOT null Then 1 End,
Case When priority3 IS NOT null Then 1 End
) priority_rank
From table_name
| priority1 | priority2 | priority3 | priority_rank |
|---|---|---|---|
| x | a | n | 1 |
| a | a | null | 2 |
| r | null | i | 3 |
| g | null | null | 4 |
| null | y | a | 5 |
| null | w | null | 6 |
| null | null | t | 7 |
| null | null | null | 8 |