RANK() is a function that assigns a rank to each row of a result set based on the value of a specified column. The rank indicates the ordinal position of each row within the result set. The
RANK() function is commonly used to display results in a specific order, such as from highest to lowest or vice versa.
The syntax for the
RANK() function is as follows:
RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )
PARTITION BYis an optional clause that divides the result set into partitions. The rank is assigned within each partition separately.
ORDER BYis a required clause that specifies the column or columns to sort the data by. You can also specify the sort order (ascending or descending) for each column.
Suppose you have a table named
sales with the following data:
You can use the
RANK() function to assign a rank to each row based on the sales column as follows:
SELECT id, product, sales, RANK() OVER (ORDER BY sales DESC) as rank FROM sales;
The result set would be:
As you can see, the
RANK() function has assigned a rank to each row based on the sales column, with the highest sales value receiving a rank of 1.
- If you receive an error message that says “Windowed functions can only appear in the SELECT or ORDER BY clauses”, make sure that you have included the
OVERclause after the
- If you receive an error message that says “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified”, make sure that you have included the
ORDER BYclause within the
RANK() function is a powerful tool for ranking rows in a result set based on the value of a specified column. By using the
PARTITION BY clause, you can also divide the result set into partitions and assign ranks within each partition separately. Remember to follow the syntax and troubleshooting tips to get the most out of the
RANK() function in SQL.