by Markus Winand.

Using Window Functions for Efficient Pagination


Window functions offer yet another way to implement pagination in SQL. This is a flexible, and above all, standards-compliant method. However, only SQL Server, the Oracle database and PostgreSQL 15+ can use them for a pipelined top-N query. MySQL, MariaDB0and DB2 (LUW) do not abort the index scan after fetching enough rows and therefore execute these queries very inefficiently.

The following example uses the window function ROW_NUMBER for a pagination query:

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

The ROW_NUMBER function enumerates the rows according to the sort order defined in the over clause. The outer where clause uses this enumeration to limit the result to the second page (rows 11 through 20).

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

The Oracle database recognizes the abort condition and uses the index on SALE_DATE and SALE_ID to produce a pipelined top-N behavior:

The WINDOW NOSORT STOPKEY operation indicates that there is no sort operation (NOSORT) and that the database aborts the execution when reaching the upper threshold (STOPKEY). Considering that the aborted operations are executed in a pipelined manner, it means that this query is as efficient as the offset method explained in the previous section.

Support of this optimization is by no means common among SQL products.

MariaDBMySQLOracle DBPostgreSQLSQL Server20072009201120132015201720192021⚠ 2008R2 - 2022a⚠ 15b⊘ 9.0 - 14⚠ 12cR1 - 21cb⚠ 11gR1 - 11gR2⊘ 8.0.18 - 8.0.34⊘ 10.2 - 10.11
  1. Only with row_number()
  2. Not with partition by clause (see below)

While this optimization could conceptually work with any monotonic function, there is a clear focus on the ROW_NUMBER function among the analyzed implementations.

MariaDBMySQLOracle DBaPostgreSQLSQL Server ROW_NUMBER() RANK() DENSE_RANK() COUNT(…)
  1. Only with where clause: OVER(ORDER BY…) + WHERE x=?

Caution needs to be taken when using partition by: Even if the where clause limits the rows to a single partition, the pure presence of partition by disables this optimization in some products. This might happen if the partitioned window function is part of a view, but the outer query limits the view to a single partition.

MariaDBMySQLOracle DBaPostgreSQLSQL Server OVER(ORDER BY…) OVER(ORDER BY…) + WHERE x=?OVER(PARTITION BY x ORDER BY…) + WHERE x=?
  1. Not all functions that work in presence of a where clause

The strength of window functions is not pagination, however, but analytical calculations. If you have never used window functions before, you should definitely spend a few hours studying the respective documentation.

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Footnotes

  1. MySQL supports window functions since version 8.0, MariaDB since 10.2.

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license