Rank and Partition

Rank and partition are two features of SQL that are used together. They are used to find the rank of a row in a group of rows. Let's see how they work.

Let's say we have a table called employee with the following data:

+----+----------+--------+------------+
| id | name     | salary | department |
+----+----------+--------+------------+
|  1 | Prasanna |  10000 | IT         |
|  2 | John     |  20000 | IT         |
|  3 | Smith    |  30000 | IT         |
|  4 | Mary     |  40000 | HR         |
|  5 | Bob      |  50000 | HR         |
|  6 | Alice    |  60000 | HR         |
+----+----------+--------+------------+

Now let's say we want to find the rank of each employee in each department based on their salary. We can do that using the following query:

SELECT id, name, salary, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employee;

The above query will give us the following result:

+----+----------+--------+------------+------+
| id | name     | salary | department | rank |
+----+----------+--------+------------+------+
|  6 | Alice    |  60000 | HR         |    1 |
|  5 | Bob      |  50000 | HR         |    2 |
|  4 | Mary     |  40000 | HR         |    3 |
|  3 | Smith    |  30000 | IT         |    1 |
|  2 | John     |  20000 | IT         |    2 |
|  1 | Prasanna |  10000 | IT         |    3 |
+----+----------+--------+------------+------+

As you can see, the rank is calculated based on the salary of the employee in each department. The employee with the highest salary in each department gets the rank 1, the employee with the second highest salary gets the rank 2 and so on.

What we did here is we partitioned the rows by department and then ordered them by salary. This means that the rows are grouped by department and then ordered by salary. The rank is calculated based on the order of the rows in each group.

Below are the various functions that can be used with the rank and partition feature:

• RANK(): This function returns the rank of each row in a group of rows. The rank is calculated based on the order of the rows in each group.
• DENSE_RANK(): This function returns the rank of each row in a group of rows. The rank is calculated based on the order of the rows in each group. The difference between this function and the RANK function is that this function does not skip any ranks. For example, if there are two rows with the same rank, then the next row will have the rank 3.
• ROW_NUMBER() :This function returns the row number of each row in a group of rows. The row number is calculated based on the order of the rows in each group.
• NTILE() :This function returns the rank of each row in a group of rows. The rank is calculated based on the order of the rows in each group. The difference between this function and the RANK function is that this function does not skip any ranks. For example, if there are two rows with the same rank, then the next row will have the rank 3.

Referrences

https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-rank-function/ https://medium.com/snowflake/how-qualify-works-with-in-depth-explanation-and-examples-bbde9fc742db