Setting rank to NULL using RANK() OVER in SQL.

Question

In a SQL Server DB, I have a table of values that I am interested in ranking.

When I perform a RANK() OVER (ORDER BY VALUE DESC) as RANK, I get the following results (in a hypothetical table):

RANK | USER_ID   | VALUE
------------------------
1   | 33        | 30000
2   | 10        | 20000
3   | 45        | 10000
4   | 12        | 5000
5   | 43        | 2000
6   | 32        | NULL
6   | 13        | NULL
6   | 19        | NULL
6   | 28        | NULL

The problem is, I do not want the rows which have NULL for a VALUE to get a rank - I need some way to set the rank for these to NULL. So far, searching the web has brought me no answers on how I might be able to do this.

Thanks for any help you can provide.

Answer 1

You can try a CASE statement:

SELECT
    CASE WHEN Value IS NULL THEN NULL
         ELSE RANK() OVER (ORDER BY VALUE DESC)
    END AS RANK,
    USER_ID,
    VALUE
FROM yourtable

Answer 2

For DESC you just need a single outer case. For ASC to avoid the NULLS affecting the RANK results you could use an inner case also.

with t as
(
select 1 as a union select 2 union select 3 union select null
)
   SELECT a,
        CASE
            WHEN a IS NOT NULL
            THEN RANK() OVER (ORDER BY
                CASE
                    WHEN a IS NULL
                    THEN 1
                    ELSE 0
                END ASC, a ASC)
        END
    FROM t