Skip to main content

Window functions: ROW_NUMBER

Premium

In SQL, the ROW_NUMBER() function is a type of window function that assigns a unique number to each row in a result set, starting at 1 for the first row. This function is used to enumerate the rows in a query result and can be useful for pagination, filtering, and ranking results.

Basic syntax for ROW_NUMBER

Here's the syntax for the ROW_NUMBER() function:

SQL
SELECT
  column1,
  ROW_NUMBER() OVER (ORDER BY column2 DESC) as row_num
FROM table_name;

The OVER clause in the ROW_NUMBER() function determines the order in which the rows are numbered. In this example, the rows are ordered by the column2 in descending order.

It is important to note that the ROW_NUMBER() function does not guarantee a stable order for rows that have the same values for the ORDER BY clause. If you need a stable order, you can add additional columns to the ORDER BY clause.

Example: Find Top Five Customers

Here's an example of how you can use the ROW_NUMBER() function to find the top 5 customers with the highest sales:

SQL
WITH customer_sales AS (
  SELECT 
    customer_id, 
    SUM(total_sales) as total_sales
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_sales,
  ROW_NUMBER() OVER (ORDER BY total_sales DESC) as row_num
FROM customer_sales
WHERE row_num <= 5;

In this example, a common table expression (CTE) named customer_sales is used to calculate the total sales for each customer. The ROW_NUMBER() function is then used to assign a unique number to each row in the customer_sales CTE, ordered by the total_sales in descending order. The final query then filters the result set to only show the top 5 rows with the highest sales.

Keep in mind that the ROW_NUMBER() function operates on the result set after it has been filtered by the WHERE clause. This means that the row numbering starts after the filter has been applied, so the row_num column may not be consecutive.

In conclusion, the ROW_NUMBER() function is a powerful tool for numbering the rows in a query result, making it easier to work with the results for pagination, filtering, and ranking.

Try it yourself

Using our example orders table, write a query that returns the 3 largest orders with their row numbers.

Next Lesson