SQL LIMIT

Summary: in this tutorial, you’ll learn to use the SQL LIMIT clause to limit the number of rows returned from a query.

Introduction to SQL LIMIT clause

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;Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • The LIMIT row_count determines the number of rows (row_count) returned by the query.
  • The 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.

SQL LIMIT clause examples

We’ll use the employees table in the sample database to demonstrate the LIMIT & OFFSET clauses.

employees_table

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;Code language: SQL (Structured Query Language) (sql)

Try It

SQL LIMIT example

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;    Code language: SQL (Structured Query Language) (sql)

Try It

SQL LIMIT 5 example

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;Code language: SQL (Structured Query Language) (sql)

Try It

SQL LIMIT OFFSET example

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;Code language: SQL (Structured Query Language) (sql)

Try It

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;Code language: SQL (Structured Query Language) (sql)

Try It

SQL LIMIT top 5 highest salary

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.

SQL LIMIT top 5 lowest salary

Getting the rows with the Nth highest value

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;Code language: SQL (Structured Query Language) (sql)

Try It

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;Code language: SQL (Structured Query Language) (sql)

Try It

SQL LIMIT 2nd highest salary

And pass the result to another query:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = 17000;Code language: SQL (Structured Query Language) (sql)

Try It

SQL LIMIT 2nd highest salary example

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);Code language: SQL (Structured Query Language) (sql)

Try It

Summary

  • Use LIMIT & OFFSET clauses to limit the number of rows returned by a query.
  • LIMIT & OFFSET is not SQL standard.

Popular posts from this blog

SQL Syntax

Image
  SQL Syntax Summary : in this tutorial, we will introduce you to the SQL syntax that helps you understand the details of SQL statements. SQL is a declarative language, therefore, its syntax reads like a natural language. An SQL statement begins with a verb that describes the action, for example, SELECT, INSERT, UPDATE or DELETE. Following the verb are the subject and predicate. A predicate specifies conditions that can be evaluated as true, false, or unknown. See the following SQL statement: SELECT first_name FROM employees WHERE YEAR (hire_date) = 2000 ; Code language: SQL (Structured Query Language) ( sql ) As you see, it reads like a normal sentence. Get the first names of employees who were hired in 2000. The  SELECT first_name ,  FROM employees , and WHERE  are clauses in the SQL statement. Some clauses are mandatory e.g., the  SELECT  and  FROM  clauses whereas others are optional such as the  WHERE  clause. Because SQL was designed specifically for the non-tec
Read more

SQL FETCH

Image
Skip to content SQL FETCH Summary : in this tutorial, you will learn how to use the SQL   FETCH   clause to limit the number of rows returned by a query. Introduction to SQL FETCH clause To limit the number of rows returned by a query, you use the  LIMIT  clause. The  LIMIT  clause is widely supported by many database systems such as MySQL, H2, and HSQLDB. However, the  LIMIT  clause is not in SQL standard. SQL:2008 introduced the  OFFSET FETCH  clause which has a similar function to the  LIMIT  clause. The  OFFSET FETCH  clause allows you to skip  N  first rows in a result set before starting to return any rows. The following shows the syntax of the SQL  FETCH  clause: OFFSET offset_rows { ROW | ROWS } FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY Code language: SQL (Structured Query Language) ( sql ) In this syntax: The  ROW  and  ROWS ,  FIRST  and  NEXT  are the synonyms. Therefore, you can use them interchangeably. The  offset_rows  is an integer number which must be
Read more