RANK WINDOW FUNCTION / SQL INTERVIEW QUESTION
#sql #windowfunctions #sqltutorial #rownumber
WHAT IS RANK WINDOW FUNCTION
The RANK window function in SQL is used to assign a unique rank to each row within a partition of a result set. It ranks rows based on the values in one or more columns. The ranking is determined by the order specified in the ORDER BY clause.
Here’s a breakdown of the RANK window function:
Syntax:
sql
RANK() OVER ([PARTITION BY column1, column2, ...] ORDER BY column3, column4, ...)
PARTITION BY: This divides the result set into partitions to which the ranking is applied. If omitted, the entire result set is treated as a single partition.
ORDER BY: This specifies the columns that define the ranking order within each partition.
How It Works:
RANK assigns the same rank to rows with equal values in the ORDER BY clause.
The next row gets a rank incremented by the number of rows with the same rank. This creates gaps in the ranking.
Example:
Let’s consider a table employees with columns department and salary:
employee_id department salary
1 IT 5000
2 IT 7000
3 HR 4000
4 HR 4000
5 IT 6000
We can use the RANK function to rank employees by salary within each department:
sql
SELECT employee_id, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
Output:
employee_id department salary rank
2 IT 7000 1
5 IT 6000 2
1 IT 5000 3
3 HR 4000 1
4 HR 4000 1
In this example:
In the IT department, employees are ranked by salary.
In the HR department, two employees with the same salary get the same rank (1), and since they share the same rank, there is no rank 2 in the HR partition.
Key Points:
RANK allows for gaps in the ranking if multiple rows have the same rank.
If you need a ranking function without gaps, you might use DENSE_RANK instead.