Curriculum
In this tutorial, you’ll learn to use the SQL LIMIT
 clause to limit the number of rows returned from a query.
To limit the number of rows returned by a select statement, you use the LIMIT
 and OFFSET
 clauses.
The following shows the syntax of LIMIT
 & OFFSET
 clauses:
SELECT column_list FROM table1 ORDER BY column_list LIMIT row_count OFFSET offset;
In this syntax:
LIMIT row_count
 determines the number of rows (row_count
) returned by the query.OFFSET offset
 clause skips the offset
 rows before beginning to return the rows.The OFFSET
 clause is optional. If you omit it, the query will return the row_count rows from the first row returned by the SELECT
 clause.
When you use the LIMIT
 clause, it is important to use an ORDER BY clause to ensure the order of rows in the result set.
Not all database systems support the LIMIT
 clause. Therefore, the LIMIT
 clause is available only in some database systems only such as MySQL, PostgreSQL, SQLite, Sybase SQL Anywhere, and HSQLDB. If you use SQL Server, you can use the SELECT TOP instead.
We’ll use the employees
 table in the sample database to demonstrate the LIMIT & OFFSET
 clauses.
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;
The following example uses the LIMIT clause to return the first 5 rows in the result set returned by the SELECT clause:
SELECT employee_id, first_name, last_name FROM employees ORDER BY first_name LIMIT 5;
The following example uses both LIMIT
 & OFFSET
 clauses to return five rows starting from the 4th row:
SELECT employee_id, first_name, last_name FROM employees ORDER BY first_name LIMIT 5 OFFSET 3;
In MySQL, you can use the shorter form of the LIMIT & OFFSET
 clauses like this:
SELECT employee_id, first_name, last_name FROM employees ORDER BY first_name LIMIT 3 , 5;
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.
Suppose you have to get employees who have the 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.
Also, if 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);
Â