cshin9 cshin9 - 2 months ago 6
SQL Question

How to not count NULL values in DENSE_RANK()?

Say I have the following table:

col
NULL
1
1
2


Then I select:

SELECT col, DENSE_RANK() OVER(ORDER BY col) as rnk from table


Then I get:

col rnk
NULL 1
1 2
1 2
2 3


What I want to get is this:

col rnk
NULL NULL
1 1
1 1
2 2


But if I query:

SELECT col, CASE WHEN col IS NOT NULL THEN DENSE_RANK() OVER(ORDER BY col) END as rnk from table


Then I get:

col rnk
NULL NULL
1 2
1 2
2 3


Is there a way to disregard
NULL
s when ranking, other than using a
WHERE
clause? I have some other columns whose rows cannot be omitted.

Answer

Use partition by:

SELECT col,
       (CASE WHEN col IS NOT NULL
             THEN DENSE_RANK() OVER (PARTITION BY (CASE WHEN col IS NOT NULL THEN 1 ELSE 2 END)
                                     ORDER BY col
                                    )
        END) as rnk
FROM table;