[SOLVED] Setting rank to NULL using RANK() OVER in SQL

Setting rank to NULL using RANK() OVER in SQL

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.

Solution

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