Curriculum
In this tutorial, you will learn how to use the SQL FETCH
 clause to limit the number of rows returned by a query.
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
In this syntax:
ROW
 and ROWS
, FIRST
 and NEXT
 are the synonyms. Therefore, you can use them interchangeably.offset_rows
 is an integer number which must be zero or positive. In case the offset_rows
 is greater than the number of rows in the result set, no rows will be returned.fetch_rows
 is also an integer number that determines the number of rows to be returned. The value of fetch_rows
 is equal to or greater than one.Because rows are stored in the table in an unspecified order, you should always use the FETCH
 clause with the ORDER BY clause to get consistent output.
Many database systems support the OFFSET FETCH
 clause including Oracle Database 12c+, PostgreSQL 10+, and Microsoft SQL Server 2012+. However, each database system implements the OFFSET FETCH
 clause differently with some variances.
The OFFSET FETCH
 clause is typically used in the client or web applications that require pagination. For example, if each page has ten rows, to get the rows of the second page, you can skip the first ten rows and returns the next ten rows.
We will use the employees
 table in the sample database for the demonstration.
The following statement returns the first employee who has the highest salary:
SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
In this example, first, the ORDER BY
 clause sort the employees by salary from high to low. TheÂ
OFFSET
 clause skips zero rows and the FETCH
 clause returns the first row.
The following statement sorts the employees by salary, skips the first five employees with the highest salary, and fetches the next five ones.
SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;