8 Examples to Master SQL Rank, Dense Rank, and Row Number Functions

They’re both different and similar.

Photo by Possessed Photography on Unsplash

SQL window functions are quite useful in many cases and provide an easy way to relate rows based on an attribute.

A window function performs a calculation across a set of table rows that are somehow related to the current row. This set can be the entire table or rows that belong to a particular group.

In this article, we’ll solve 8 examples to obtain a comprehensive understanding of the three frequently used window functions, which are:

• ROW_NUMBER
• RANK
• DENSE_RANK

In the examples, we’ll be using the employees table shown below:

employees table (image by author)

Example 1 — ROW_NUMBER

The ROW_NUMBER function assigns a sequential integer to each row in a result set.

In the following query, a row number is assigned to each row based on the values in the salary column. By default, values are ordered in ascending order so the lowest salary is assigned a row number of 1.

SELECT 
  employee_id…