How to not count NULL values in DENSE_RANK()?

Question

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 NULLs when ranking, other than using a WHERE clause? I have some other columns whose rows cannot be omitted.

BQ or Teradata? They are very different. Tag correctly! – Gordon Linoff Sep 28 '16 at 20:15 — Gordon Linoff, Sep 28 '16 at 20:15
BQ now supports standard SQL. – cshin9 Sep 28 '16 at 20:16 — cshin9, Sep 28 '16 at 20:16
"standard SQL" <> "Teradata SQL" – Gordon Linoff Sep 28 '16 at 20:17 — Gordon Linoff, Sep 28 '16 at 20:17

score 6 · Accepted Answer · edited Sep 28 '16 at 20:49

6

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;

edited Sep 28 '16 at 20:49

Mikhail Berlyant

165,386
8
154
230

answered Sep 28 '16 at 20:16

Gordon Linoff

1,242,037
58
646
786

1

just to clarify - this answer is for BigQuery Standard SQL – Mikhail Berlyant Sep 28 '16 at 20:49
@MikhailBerlyant . . . This query will work in any SQL engine that supports `dense_rank()`, as far as I know. – Gordon Linoff Sep 28 '16 at 22:17
but not with BigQuery Legacy SQL :o) as PARTITION BY in legacy SQL allows ONLY field name and not expression – Mikhail Berlyant Sep 28 '16 at 22:19
@MikhailBerlyant . . . Duly noted. It is has been a while since I've used the legacy interface. Glad they fixed it. – Gordon Linoff Sep 28 '16 at 22:20

score 0 · Answer 2 · answered Sep 28 '16 at 20:58

0

Below is for BigQuery Legacy SQL

SELECT col, CASE WHEN col IS NOT NULL THEN rnk END AS rnk
FROM (
  SELECT 
    col, (col IS NULL) AS tmp, 
    DENSE_RANK() OVER(PARTITION BY tmp ORDER BY col) AS rnk 
  FROM table
)

answered Sep 28 '16 at 20:58

Mikhail Berlyant

165,386
8
154
230

score 0 · Answer 3 · answered Jul 29 '23 at 18:14

select 
col,
case 
   when col is not null -- to override dense_rank 3 to NULL for the NULL record
   then dense_rank() over (order by col nulls last)
   end as rnk
from table 
order by col asc
;

gives result

       COL        RNK
---------- ----------
                     
         1          1
         1          1
         2          2

How to not count NULL values in DENSE_RANK()?

3 Answers3