Setting rank to NULL using RANK() OVER in SQL

sql null rank
Tom Schneider picture Tom Schneider · Sep 9, 2010 · Viewed 14.9k times · Source

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

Mark Byers picture Mark Byers · Sep 9, 2010

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

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More
NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More