Search code examples
sql-serverranking

SQL Server Rank() Cap for each column

I have a SQL query that has 3 int columns, and then 3 Rank() functions with a partition by that ranks those 3 columns.

The question is, I just want to rank up to 5000 for each column, and then stop after that. Is there anyway to do this in a single query without splitting it up into 3 queries? I still want ALL of the data for the first 3 columns, just want Rank() columns to go null after 5000.

Each partition would go up to 5000.

Thanks

Solution

  • You could use a case statement.

    ...
CASE WHEN ROW_NUMBER() OVER (<partition by>) <= 5000 
     THEN <rank function> 
     ELSE NULL 
END
...