Exclude null values using DENSE_RANK

Question

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

GriGrim · Accepted Answer · 2016-10-11T19:06:19.763

11

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

edited Oct 11 '16 at 19:06

answered Oct 11 '16 at 18:54

GriGrim

2,891
1
19
33

I think you need an extra set of `(` `)` around the case. – Hogan Oct 11 '16 at 18:57
No. Just checked. They are not needed. – GriGrim Oct 11 '16 at 19:01
Works like a charm! – Omer Mar 13 '18 at 10:49

score 7 · Answer 2 · answered Oct 11 '16 at 18:42

7

You can use case like this:

select A,
       (case when A is not null
             then dense_rank() over (partition by (case when A is not null then 1 else 0 end)
                                     order by a desc
                                    )
        end) as dr
from A1;

answered Oct 11 '16 at 18:42

Gordon Linoff

1,242,037
58
646
786

When A is null, what happens to the data returned? – Ross Bush Oct 11 '16 at 18:43
I'm sorry I didnt not post the question correctly, please go back and take a look at the edits. A is never null, 'Its the Dense_R that is causing the issue – ThatRiddimGuy Oct 11 '16 at 18:44
@RossBush Nothing. `dr` will be `null` because there no `else` mentioned. – GriGrim Oct 11 '16 at 18:44
@kage77 `partition by` from this query will solve your problem – GriGrim Oct 11 '16 at 18:46
@GriGrim Can you please elaborate? Partition over what? – ThatRiddimGuy Oct 11 '16 at 18:47
Thanks @Hogan, but if you see the edits I made 'A' is never null, its the null in 'DENSE_R' thats causing the issue. – ThatRiddimGuy Oct 11 '16 at 18:50
@Hogan its a NULL because of the 'when' condition in the case statement – ThatRiddimGuy Oct 11 '16 at 18:54
@kage77 - oh! I understand now. See GriGrim's answer. You just use the same condition. – Hogan Oct 11 '16 at 18:56

Exclude null values using DENSE_RANK

2 Answers2

Linked