SQL LIMIT

Summary: This tutorial shows you how to use the SQL LIMIT clause to constrain the number of rows returned by a SELECT statement.

Introduction to SQL LIMIT clause

To retrieve a portion of rows returned by a query, you use the LIMIT and OFFSET clauses. The following illustrates the syntax of these clauses:

SELECT 
    column_list
FROM
    table1
ORDER BY column_list
LIMIT row_count OFFSET offset;

In this syntax:

• The row_count determines the number of rows that will be returned.
• The OFFSET  clause skips the offset rows before beginning to return the rows. The OFFSET a clause is optional so you can skip it. If you use both LIMIT and OFFSET clauses the OFFSET  skips offset rows first before the LIMIT  constrains the number of rows.

When you use the LIMIT clause, it is important to use an ORDER BY clause to make sure that the rows in the returned are in a specified order.

Not all database systems support the LIMIT clause, therefore, the LIMIT a clause is available only in some database systems only such as MySQL, PostgreSQL, SQLite, Sybase SQL Anywhere, and HSQLDB.

SQL LIMIT clause examples

We will use the employees the table in the sample database to demonstrate the LIMIT clause.

The following statement returns all rows in the employees table sorted by the first_name column.

SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name;

To return just the top 5 rows, you use the LIMIT clause as the following statement.

SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5;

To skip two rows and get the next five rows, you use both LIMIT and OFFSET clauses as shown in the following statement.

SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5 OFFSET 3;

If you are using MySQL, you can use the shorter form of the LIMIT OFFSET clauses.

SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 3 , 5;

Using SQL LIMIT to get the top N rows with the highest or lowest value

You can use the LIMIT clause to get the top N rows with the highest or lowest value. For example, the following statement gets the top five employees with the highest salaries.

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
ORDER BY salary DESC
LIMIT 5;

First, the ORDER BY clause sorts the employees by salary in descending order and then the LIMIT clause restricts five rows returned from the query.

To get the top five employees with the lowest salary, you sort the employees by salary in the ascending order instead.

Getting the rows with the Nth highest value

Suppose you have to get employees who has 2nd highest salary in the company. To do so, you use the LIMIT OFFSET clauses as follows.

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

The ORDER BY clause sorts the employees by salary in descending order. And the LIMIT 1 OFFSET 1 clause gets the second row from the result set.

This query works with the assumption that every employee has a different salary. It will fail if there are two employees who have the same highest salary. In addition, in case you have two or more employees who have the same 2nd highest salary, the query just returns the first one.

To fix this issue, you can get the second highest salary first using the following statement.

SELECT DISTINCT
    salary
FROM
    employees
ORDER BY salary DESC
LIMIT 1 , 1;

And pass the result to another query:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = 17000;

If you know subquery, you can combine both queries into a single query as follows:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = (SELECT DISTINCT
            salary
        FROM
            employees
        ORDER BY salary DESC
        LIMIT 1 , 1);

In this tutorial, we have introduced you to the SQL LIMIT and OFFSET clauses that allow you to constrain the number of rows returned by a query.