How to Limit the Rows Returned by a SQL Query

When writing SQL queries, we’ll often use a WHERE clause or HAVING clause to narrow the results down to just those rows that we’re interested in.

But sometimes we might want to reduce the number of rows returned without adding extra filtering criteria. Sometimes we might just want to see a handful of rows, without hundreds, thousands or even millions of rows being returned.

For example, we might just want to see the first five rows returned by a query, even though the query would normally return thousands of rows.

Most major RDBMSs provide us with at least one method for doing this. The options available to you will depend on your RDBMS, but some of the most common options include the LIMIT clause, the FETCH clause, and the TOP() clause. Some RDBMSs also implement various settings that limit the number of rows returned from queries.

Below are examples of limiting the rows returned by a SQL query in various RDBMSs.

The `LIMIT` Clause

The SQL LIMIT clause is implemented in some of the most popular RDBMSs, such as MySQL, MariaDB, PostgreSQL, and SQLite.

Example:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
LIMIT 3;

Result:

+-----------+--------------------------+
| ProductId | ProductName              |
+-----------+--------------------------+
|         1 | Left handed screwdriver  |
|         2 | Right handed screwdriver |
|         3 | Long Weight (blue)       |
+-----------+--------------------------+
3 rows in set (0.00 sec)

All I did was add LIMIT 3 to the end of the query. This caused the query to return just three rows.

The table actually contains ten rows. We can verify this with the SQL COUNT() function:

SELECT COUNT(*)
FROM Products
ORDER BY ProductId;

Result:

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

As expected, it indicates that there are ten rows in the table.

If the query would normally return less than the amount specified in the LIMIT clause, then the LIMIT clause would have no impact on the result.

Most RDBMSs that support the LIMIT clause also allow us to include an OFFSET clause. The OFFSET clause allows us to skip a certain number of rows at the beginning of the result set. In this case, the LIMIT clause only applies to the rows that follow that offset.

Example:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
LIMIT 3 OFFSET 2;

Result:

+-----------+---------------------+
| ProductId | ProductName         |
+-----------+---------------------+
|         3 | Long Weight (blue)  |
|         4 | Long Weight (green) |
|         5 | Sledge Hammer       |
+-----------+---------------------+
3 rows in set (0.00 sec)

Here, I used the OFFSET clause to skip the first two rows. The LIMIT clause now applies to the rows that follow. So we still get three rows, but it’s a different set of three rows.

In some RDBMSs, it’s possible to use a short form syntax, like this:

SELECT
    ProductId,
    ProductName
FROM Products
ORDER BY ProductId
LIMIT 2, 3;

In this case the offset comes first, followed by the limit amount.

The exact syntax may depend on your RDBMS, but your RDBMSs will most likely support one (if not both) of the above (assuming it supports the LIMIT clause to begin with).

The `FETCH` Clause

If your RDBMS doesn’t support the LIMIT clause, it may implement a FETCH clause. Examples include SQL Server and Oracle.

The FETCH clause works in a similar way to the LIMIT clause:

Example:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;

Result:

ProductId  ProductName               ProductPrice
---------  ------------------------  ------------
1          Left handed screwdriver   25.99       
2          Right handed screwdriver  25.99       
3          Long Weight (blue)        14.75       

3 row(s) returned

I ran this example in SQL Server, which requires the OFFSET clause, even if it’s set to zero. Oracle on the other hand allows us to omit the OFFSET clause altogether (although we can still use it if required).

Suffice to say, we can use the OFFSET clause to skip rows, just like we did with the LIMIT clause.

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;

Result:

ProductId  ProductName          ProductPrice
---------  -------------------  ------------
3          Long Weight (blue)   14.75       
4          Long Weight (green)  11.99       
5          Sledge Hammer        33.49       

3 row(s) returned

So this time we still get three rows, it’s just a different set of three rows.

The `TOP()` Clause

SQL Server also has a TOP() clause that’s designed specifically for limiting the rows returned by a query. It can also be used to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement.

Here’s an example of using TOP() in our SELECT query:

SELECT
    TOP(3) 
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId;

Result:

ProductId  ProductName               ProductPrice
---------  ------------------------  ------------
1          Left handed screwdriver   25.99       
2          Right handed screwdriver  25.99       
3          Long Weight (blue)        14.75       

3 row(s) returned

So we can see that SQL Server’s TOP() clause provides us with a quick and easy way to limit the results of our SQL queries. However, we can’t use TOP() and OFFSET in the same query (or sub-query). So if you need to set an offset, use the FETCH clause instead.

The `ROWNUM` Pseudocolumn

Some RDBMSs (such as Oracle, MariaDB) include a ROWNUM pseudocolumn that provides a number indicating the order in which the row is selected from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

We can therefore use this to limit the number of rows returned by a query:

SELECT
    ProductId,
    ProductName
FROM Products
WHERE ROWNUM <= 3
ORDER BY ProductId;

Result:

PRODUCTID	PRODUCTNAME
1	Left handed screwdriver
2	Right handed screwdriver
3	Long Weight (blue)

Here, I specified that the query should only return rows that have a ROWNUM of 3 or less.

The `ROW_NUMBER()` Window Function

Many RDBMSs have a ROW_NUMBER() window function that can be used in a similar way to ROWNUM to reduce the number of rows returned by a query.

The exact syntax used will depend on your RDBMS, but here’s an example in Oracle:

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER(ORDER BY ProductId) AS rn,
        ProductId,
        ProductName
    FROM Products
    )
WHERE rn <= 3
ORDER BY ProductId;

Result:

RN	PRODUCTID	PRODUCTNAME
1	1	Left handed screwdriver
2	2	Right handed screwdriver
3	3	Long Weight (blue)

Settings that Limit Query Output

Depending on your RDBMS, you may also have other options for limiting the results returned by your SQL queries.

For example, SQL Server has the SET ROWCOUNT setting that allows us to specify a limit on all queries. When we use this option, we don’t need to specify TOP() or FETCH in our query (unless we need to further limit the results returned).

Here’s an example using SET ROWCOUNT in SQL Server:

SET ROWCOUNT 3;

Result:

OK

Now, all subsequent queries will be limited to just three rows:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId;

Result:

ProductId  ProductName               ProductPrice
---------  ------------------------  ------------
1          Left handed screwdriver   25.99       
2          Right handed screwdriver  25.99       
3          Long Weight (blue)        14.75       

3 row(s) returned

We can remove the limitation by using a value of zero:

SET ROWCOUNT 0;

Result:

OK

Such setting should be implemented with caution. If you forget that you’ve set a limit, your queries could return incomplete data without you knowing it.

Also, given SET ROWCOUNT is set outside of a statement that runs a query, its value can’t be considered in a query plan for query optimization. On the other hand, the value of the given expression in the TOP() or FETCH clauses can be considered for query optimization.

The LIMIT Clause

The FETCH Clause

The TOP() Clause

The ROWNUM Pseudocolumn

The ROW_NUMBER() Window Function

Settings that Limit Query Output

The `LIMIT` Clause

The `FETCH` Clause

The `TOP()` Clause

The `ROWNUM` Pseudocolumn

The `ROW_NUMBER()` Window Function