Exploring SQL Window Functions: ROW_NUMBER, RANK, and DENSE_RANK

In the world of SQL, window functions are a powerful tool that allows you to perform calculations across a set of table rows related to the current row. In this article, we’ll dive into three essential SQL window functions: ROW_NUMBER, RANK, and DENSE_RANK. These functions enable you to assign unique numbers or ranks to rows within result sets, making it easier to extract valuable insights from your data.

Photo from Pexels

1. ROW_NUMBER Function : Creating Rankings

The ROW_NUMBER() function assigns a unique value to each row within a result set. This function is handy when you need to distinguish between rows but don’t require distinct ranking. It can be beneficial for pagination, data deduplication, or creating unique identifiers.

General Syntax:
ROW_NUMBER() OVER (ORDER BY column)

• ORDER BY specifies the column or expression used for ordering the result set.

ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)

• PARTITION BY is an optional clause that divides the result set into partitions based on the specified column. The ranking is applied separately within each partition.

Let’s assign employees with new employee IDs in descending order according to their salaries:

SELECT * , ROW_NUMBER() OVER(ORDER BY salary DESC) AS new_employee_id
FROM employees;

Let’s rank employees based on their salaries in descending order, and divide the result set into partitions based on the ‘position’ column.

SELECT * , ROW_NUMBER() OVER(PARTITION BY position ORDER BY salary DESC) AS employee_rank
FROM employees;

2. RANK Function

The RANK() function assigns a unique rank to each row based on the values in one or more columns. Rows with the same values receive the same rank…