CODEWITHSUNDEEP
sqlpostgresql
Heisenberg
Heisenberg

Reputation: 5279

Add priority columns by refering to each col information

I have following tables , I would like to distinguish each cols by null or is not null.

priority1 priority2 priority3 ...
a          a        null
a          null     null
null       null     null
a          null     a
a          a        null
null       a        a
null       null     a
null       a        a

so my desired result is like this , I would like to set priority and I would like to add priority_rank as follows.

priority1 priority2 priority3 ... priority_rank
a          a        a             1
a          a        null          2
a          null     a             3
a          null     null          4
null       a        a             5
null       a        null          6
null       null     a             7
null       null     null          8

I tried following.

select case 
when priority1 is not null and priority2 is not null and priority3 is not null then priority_rank=1,
when priority1 is not null and priority2 is not null and priority3 is null then priority_rank=2,
・・・

But my concern is that when the priority column increase, this case increase exponentially, so that from now on 2^^3 = 8 select clause are needed.

Are there any easy ways to achieve this ?

If someone has opinion,will you please let me know. Thanks

Upvotes: 0

Views: 111

Answers (4)

Alex Bran
Alex Bran

Reputation: 29

ORDER BY + ROW_NUMBER() OVER() sort and assign rank

SELECT *
, ROW_NUMBER() OVER(ORDER BY priority1 IS NULL,priority2 IS NULL,priority3 IS NULL) AS priority_rank
FROM table_name
ORDER BY priority1 IS NULL, priority2 IS NULL, priority3 IS NULL

Upvotes: -1

user3408245
user3408245

Reputation:

My apologies I was able to create this on SSMS with pretty basic ANSI SQL so I think it might work. It will still need some maintenance if you add more columns.

SELECT
*
, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS PRIORITY_RANK
FROM
(
SELECT 
        Priority1
      , Priority2
      , Priority3
      , ISNULL(LEN(Priority1),0) + ISNULL(LEN(Priority2),0) + ISNULL(LEN(Priority3),0) AS TotalPriority
      
  FROM PriorityTableTest

) AS X

ORDER BY TotalPriority DESC, Priority1 DESC, Priority2 DESC, Priority3 DESC

Upvotes: 1

Alexey
Alexey

Reputation: 2479

Assuming that there can be different values ​​in the priority columns, and the higher the value, the higher the overall priority, and that the rows with the same priority values should have the same priority_rank value in the results, you just can use DENSE_RANK with ORDER BY for all priority values in descending order. Since PostgreSQL uses NULLS LAST for ascending ordering by default, than with descending order NULL values would appear first. To prevent this you need to add NULLS LAST to all order statements.

Finally, your query can look like this

select 
    *, 
    dense_rank() over (
        order by priority1 desc nulls last, 
                 priority2 desc nulls last, 
                 priority3 desc nulls last
    ) priority_rank 
from priorities

For the sample data you provided the output is

priority1 priority2 priority3 priority_rank
a a null 1
a a null 1
a null a 2
a null null 3
null a a 4
null a a 4
null null a 5
null null null 6

db<>fiddle here

Upvotes: 1

ahmed
ahmed

Reputation: 9181

You may use the DENSE_RANK() function as the following:

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

See a demo.

Note that null values are sorted last in the default ascending order.

Upvotes: 1

Related Questions