On Data Engineering

The versatility of ROW_NUMBER, one of SQL’s greatest function

Understanding how to leverage one of the most useful window functions in SQL’s toolbox

ROW_NUMBER is one of the most valuable and versatile functions in SQL. It can be leveraged for different use cases, from ranking items, identifying data quality gaps, doing some minimization, handling preference queries, or helping with sessionization etc.

Photo by R Mo on Unsplash

The ROW_NUMBER function isn’t, however, a traditional function. It is a window function. Window functions are an advanced kind of function, with specific properties. This article aims to go over how window functions, and more specifically, how the ROW_NUMBERfunction work, and to go over some of the use cases for the ROW_NUMBER function.

Anatomy of a Window function

To understand how a window function work, it is essential first to understand, what type of arguments it can take. The following is the syntax for providing an argument using the window function.

ROW_NUMBER() OVER (window)

As you can see, the row number doesn’t take a direct argument. We don’t have a ROW_NUMBER(a.columna) , for instance, but takes arguments in the…