kage77 kage77 - 1 month ago 5
SQL Question

Exclude null values using DENSE_RANK

Dense_Rank is taking everything into account. Is there a way to exclude the null values so the next rank after 1 would be 2 and not 3.

This is what the table looks like now:

A | DENSE_R
--------------
1 | 1
--------------
2 | null
--------------
3 | 3
--------------
4 | 4


This is what I want the table to look like:

A | DENSE_R
--------------
1 | 1
--------------
2 | null
--------------
3 | 2
--------------
4 | 3


I'm using the following code to do so:-

WITH CTE AS
(
SELECT A
FROM A1
)
SELECT A,
CASE
WHEN **Condition**
THEN DENSE_RANK() OVER (Order by [A] ASC)
END
AS 'DENSE_R'
FROM CTE

Answer

Use partition by the same **Condition** as you used already.

WITH CTE AS
(
 SELECT A 
 FROM A1
)
SELECT A,
CASE 
  WHEN  **Condition**
  THEN DENSE_RANK() OVER (Partition by (case when **Condition** then 1 else 0 end) Order by [A] ASC)
END
AS 'DENSE_R'
FROM CTE
Comments