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

The CASE statement provided earlier would count the NULL records in the rank if the SORT BY was ascending rather than descending. This would start the ranking at 5 rather than 1 - probably not what is desired.

To ensure that the nulls do not get counted in the rank, you can force them to the bottom by adding an initial sort criteria on whether the value IS NULL or not, like so:

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

*** credit to Hugo Kornelis: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/deb8a0aa-aaab-442b-a667-11220333a4e0/rank-without-counting-null-values?forum=transactsql