Add priority columns by refering to each col information-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.