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

Postgresql – 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

Tags:

4

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    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

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

Back To Top
Search