Top-N and Pagination Queries

Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way.

Example

Let’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in the skijump_results table. There is no index on the skijump_results table. The following queries are examples of a Top-N and pagination query, which I will use in the following parts of the article.

1. Select the top 3 best ski jumpers, sorted by the number of points in descending order.
2. Select from 2nd to 4th ski jumpers, sorted by the number of points in descending order.

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 32 | Michael Hayboeck   | AUT     | 258.0  |
+----+--------------------+---------+--------+
| 42 | Thomas Diethart    | AUT     | 258.3  |
+----+--------------------+---------+--------+
| 43 | Andreas Wellinger  | GER     | 257.1  |
+----+--------------------+---------+--------+
| 44 | Anders Bardal      | NOR     | 264.1  |
+----+--------------------+---------+--------+
| 49 | Peter Prevc        | SLO     | 265.3  |
+----+--------------------+---------+--------+
| 50 | Kamil Stoch        | POL     | 278.0  |
+----+--------------------+---------+--------+

MySQL

To get top 3 ski jumpers in MySQL, you use a LIMIT clause:

| select * 
| from skijump_results
| order by points
| limit 3;

The result:

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 50 | Kamil Stoch        | POL     | 278.0  |
+----+--------------------+---------+--------+
| 49 | Peter Prevc        | SLO     | 265.3  |
+----+--------------------+---------+--------+
| 44 | Anders Bardal      | NOR     | 264.1  |
+----+--------------------+---------+--------+

Moreover, the LIMIT clause lets you implement table paging by specifying a limiting range. If you wanted to select ski jumpers from 2nd to 4th place in MySQL you would write:

| select * 
| from skijump_results
| order by points
| limit 1 ,3;

The result:

+----+--------------------+---------+--------+
| id | name               | country | points |
+----+--------------------+---------+--------+
| 49 | Peter Prevc        | SLO     | 265.3  |
+----+--------------------+---------+--------+
| 44 | Anders Bardal      | NOR     | 264.1  |
+----+--------------------+---------+--------+
| 42 | Thomas Diethart    | AUT     | 258.3  |
+----+--------------------+---------+--------+

The first argument of the LIMIT clause specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

SQLite

The SQLite syntax for Top-N and pagination queries is the same as MySQL.

PostgreSQL

Fortunately, PostgreSQL uses the same clause for limiting query results as MySQL:

| select * 
| from skijump_results
| order by points
| limit 3;

However, selecting ski jumpers from 2nd to 4th place in PostgreSQL differs from MySQL. In PostgreSQL, you should use the OFFSET clause in addition to the LIMIT clause:

      
| select * 
| from skijump_results
| order by points
| limit 3 offset 1;

When both OFFSET and LIMIT appear, the OFFSET rows are skipped before starting to count the limit rows that are returned.

DB2

Here the similarities end. As I mentioned before, every database provides the ability to limit query results in its own way, as you will see with this DB2 example.

In DB2 you use the FETCH FIRST ROWS ONLY clause:

| select *
| from skijump_results
| order by points
| fetch first 3 rows only;

By default, DB2 does not support table paging unless you dig into DB2 configuration. If you set the DB2_COMPATIBILITY_VECTOR to MYS and restart the database you can use MySQL syntax for table pagination (how obvious!):

| select *
| from skijump_results
| order by points
| limit 3 offset 1;

Oracle

A description of Top-N and pagination queries in Oracle can be found in my previous article.

SQL Server

To get the top 3 ski jumpers in Transact SQL use the TOP clause:

| select top(3) *
| from skijump_results
| order by points;

Easy work. Now let’s move to implementing pagination queries. If you think it’s as easy as Top-N query, then you’re wrong. But, since SQL Server 2005, there is a ROW_NUMBER() clause (yes, like in Oracle). ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Using the ROW_NUMBER() clause, you can select ski jumpers from 2nd to 4th place like this:

| select *
| from (select ROW_NUMBER() over (order by points) as row_number, *
|       from skijump_results) as table
| where table.row_number > 1 and table.row_number <= 4
| order by table.points;