How to Limit the Number of Rows Returned by SQL Query After Ordering?

1. Overview

In SQL, limiting the number of rows returned by a query is a common requirement. For instance, there are situations where we may need to implement pagination in an application or fetch only the top-performing records. However, different database management systems handle this task differently.

In this tutorial, we’ll show how to limit the number of rows returned by an SQL query after applying an ORDER BY clause. Specifically, we’ll work in MySQL, PostgreSQL, and SQL Server.

For demonstration purposes, we use the the Baeldung University Schema.

2. The ORDER BY Clause

To begin with, let’s see how the ORDER BY clause affects an example query.

So, we focus on the Student table of the Baeldung University Schema:

> SELECT * FROM Student;
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| 1001 | John Liu        |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora        |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose     |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
| 1010 | Samantha Prabhu |  3217165566 | 2001-03-21 | 2020-01-15      | 2024-06-15      |  4.9 |
| 1011 | Vikas Jain      |   321345662 | 2001-07-18 | 2020-01-15      | NULL            |  3.3 |
| 1101 | Jia Grey        |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98 |
...
25 rows in set (0.00 sec)

Next, let’s use the ORDER BY clause to sort the rows in the Student table:

> SELECT * FROM Student ORDER BY name;
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| 2006 | Agatha Christi  |  1100245767 | 2003-05-19 | 2022-01-15      | NULL            | 4.59 |
| 1110 | Albert Decosta  |  2617897011 | 2001-02-21 | 2020-01-15      | 2024-06-15      |    4 |
| 1101 | Jia Grey        |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98 |
| 1001 | John Liu        |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 2008 | Julia Roberts   |  1212446677 | 2003-06-12 | 2022-01-15      | 2025-06-15      | 3.04 |
| 1717 | Param Mohan     |  1023456545 | 2002-05-15 | 2021-01-15      | 2025-06-15      | 2.75 |
...
25 rows in set (0.00 sec)

Above, the query uses the name column to sort the rows alphabetically.

Now, let’s limit the number of rows returned by the query after ordering.

3. Using MySQL and Limits

MySQL supports the LIMIT clause which can help limit the returned rows. In particular, the LIMIT clause enables us to specify the number of rows to retrieve as well as an optional offset from which to start.

Let’s show an example of the syntax:

> SELECT *
FROM table_name
ORDER BY column_name
LIMIT offset_value, number_of_rows;

To limit the number of rows we make use of two parameters:

• offset_value is the number of rows to skip before the query starts to fetch rows (optional)
• number_of_rows is a value representing the number of rows to fetch

Now, let’s use this syntax to demonstrate how to fetch 4 rows starting from the 2nd row:

> SELECT *
FROM Student
ORDER BY name
LIMIT 1, 4;
+------+----------------+-------------+------------+-----------------+-----------------+------+
| id   | name           | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+----------------+-------------+------------+-----------------+-----------------+------+
| 1110 | Albert Decosta |  2617897011 | 2001-02-21 | 2020-01-15      | 2024-06-15      |    4 |
| 1101 | Jia Grey       |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98 |
| 1001 | John Liu       |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 2008 | Julia Roberts  |  1212446677 | 2003-06-12 | 2022-01-15      | 2025-06-15      | 3.04 |
+------+----------------+-------------+------------+-----------------+-----------------+------+
4 rows in set (0.00 sec)

This query sorts the rows in the Student table by the name column, skips the first row, and then retrieves the next 4 rows. Therefore, if we remove the offset_value (1), the query retrieves rows starting from the first row.

4. Using PostgreSQL and Limits

In PostgreSQL, we combine the LIMIT and OFFSET clauses to limit the number of rows:

> SELECT *
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET offset_value;

Let’s see the breakdown:

• LIMIT number_of_rows defines the number of rows the query fetches
• OFFSET offset_value declares the number of rows to skip before the query starts to fetch rows

Now, we use this syntax in a practical example:

> SELECT *
FROM Student
ORDER BY name
LIMIT 4 OFFSET 2;
  id  |     name      | national_id | birth_date | enrollment_date | graduation_date | gpa  
------+---------------+-------------+------------+-----------------+-----------------+------
 1101 | Jia Grey      |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98
 1001 | John Liu      |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4
 2008 | Julia Roberts |  1212446677 | 2003-06-12 | 2022-01-15      | 2025-06-15      | 3.04
 1717 | Param Mohan   |  1023456545 | 2002-05-15 | 2021-01-15      | 2025-06-15      | 2.75
(4 rows)

Thus, this query skips the first 2 rows and fetches the next 4 rows.

5. Using SQL Server and Limits

In SQL Server, we can utilize a construct that Microsoft introduced with SQL Server 2012: OFFSET-FETCH.

In particular, the OFFSET-FETCH clause can limit rows and specify an offset:

> SELECT *
FROM table_name
ORDER BY column_name
OFFSET offset_value ROWS
FETCH NEXT number_of_rows ROWS ONLY;

OFFSET-FETCH is considered a single clause that consists of the OFFSET and FETCH clauses:

• OFFSET offset_value ROWS skips the number of rows that offset_value specifies in the sorted rows
• FETCH NEXT number_of_rows ROWS ONLY retrieves the next rows that number_of_rows specifies after skipping the ones that offset_value declares

So, let’s see how we can use this clause to implement the limiting of rows:

> SELECT *
FROM Student
ORDER BY name
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;
  id  |     name      | national_id | birth_date | enrollment_date | graduation_date | gpa  
------+---------------+-------------+------------+-----------------+-----------------+------
 1101 | Jia Grey      |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98
 1001 | John Liu      |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4
 2008 | Julia Roberts |  1212446677 | 2003-06-12 | 2022-01-15      | 2025-06-15      | 3.04

The query above skips the first 2 rows and retrieves the next 3 rows.

6. Common Use Cases

There are several common use cases for limiting the number of rows returned by SQL queries after applying the ORDER BY clause.

6.1. Pagination

To begin with, we can implement pagination on Web applications. For large datasets such as product lists and blog posts, it’s impractical to load all rows at once. Instead, we can specify a query to limit the number of rows displayed per page by fetching only a subset of records depending on the requested page.

For instance, the query may limit results to 10 records per page and offset by a value determined by the requested page. This can help us enhance user experience and improve the loading time.

6.2. Top Data Performers

As another example, we can report on top-performing records. For this, we need a query that fetches the top N results.

For example, the query can retrieve the 5 top-performing products based on their sales figures or the 5 top-performing students based on their GPA. Therefore, combining the ORDER BY clause with limiting rows ensures that the top N records are presented in an ordered and optimized way. This can assist us in analyzing data as well as making business decisions.

6.3. Improve Query Performance

Further, we’re able to improve performance. Of course, querying an entire table can result in performance issues.

However, when we limit the number of rows a query returns, the database can respond to user queries quickly without overburdening the system.

6.4. Testing

Lastly, we can ensure faster testing during development. At times, we may need to work with a small subset of data during debugging. Thus, a query that limits the number of rows enables faster testing.

In addition, it prevents the loading of large datasets that are unnecessary for initial testing.

7. Conclusion

In this article, we dove into queries that can help us limit the number of rows a query returns after ordering in SQL.

In all cases, we leveraged the LIMIT clause in MySQL. For PostgreSQL, we utilized a combination of the LIMIT and OFFSET clauses. Also, we used the OFFSET-FETCH clause in SQL Server. For SQL Server, we saw that the OFFSET-FETCH clause requires SQL Server 2012 or later. By understanding these constructs, we can improve many SQL queries. For instance, it can help us implement pagination and also optimize how we fetch data by retrieving only the needed parts.