How to efficiently assign a database temporary sequence of values to rows

Motivation:

Window functions such as ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE() are knowns as ranking functions and they are useful for temporary associating to rows different kind of sequences of values that are calculated dynamically when the query is executed. They can be combined with OVER, ORDER BY, PARTITION BY and CTEs (Common Table Expressions) for resolving a wide-range of problems. The ROW_NUMBER() window function generate a sequence of temporary values from 1 to 1. Speaking about problems, ROW_NUMBER() can be used for pagination or for calculating top N rows of every group. Let's see how ROW_NUMBER() works.

Description:

The ROW_NUMBER() window function produces a sequence of values that starts from value 1 with an increment of 1. This is a temporary sequence of values (non-persistent) that is calculated dynamically at query execution time. The syntax of this window function is:

ROW_NUMBER() OVER (<partition_definition> <order_definition>)

The OVER clause defines the window of rows that ROW_NUMBER() operates on. The PARTITION BY clause (<partition_definition>) is optional and is used to break the rows into smaller sets (without it the entire result set is considered a partition). Its syntax is:

PARTITION BY <expression>,[{,<expression>}...]

The purpose of the ORDER BY clause (<order_definition>) is to set the orders of rows. The sequence of values is applied following this order (in other words, the window function will process the rows in this order). Its syntax is:

ORDER BY <expression> [ASC|DESC],[{,<expression>}...]

This window function is available in almost all databases, and starting with version 8.x is available in MySQL as well.

The following query is an example of assigning a database temporary sequential number to rows via the window function, ROW_NUMBER().

Key points:

• Because we want to fetch the ROW_NUMBER() result as well, let's write a Spring projection (DTO) that contains a getter for the column generated by ROW_NUMBER():

• Write a native query relying on ROW_NUMBER() window function:

Testing time:

Tam Ta Da Dam! :) The complete application is available on GitHub.

More examples of using ORDER BY are available in the complete source code. Most probably, you don't need to fetch the column generated by ROW_NUMBER(). You will use it internally in the query via the WHERE clause and/or CTEs. For example, How To Efficiently Finding Top N Rows Of Every Group shows you an usage case.

If you need a deep dive into the performance recipes exposed in this repository then I am sure that you will love my book "Spring Boot Persistence Best Practices".