The
OFFSET
and FETCH
clauses are the options of the order by clause. They allow you to limit the number of rows to be returned by a query.
The following illustrates the syntax of the
OFFSET
and FETCH
clauses:
In this syntax:
- The
OFFSET
clause specifies the number of rows to skip before starting to return rows from the query. Theoffset_row_count
can be a constant, variable, or parameter that is greater or equal to zero. - The
FETCH
clause specifies the number of rows to return after theOFFSET
clause has been processed. Theoffset_row_count
can a constant, variable or scalar that is greater or equal to one. - The
OFFSET
clause is mandatory while theFETCH
clause is optional. Also, theFIRST
andNEXT
are synonyms respectively so you can use them interchangeably. Similarly, you can use theFIRST
andNEXT
interchangeably.
The following illustrates the
OFFSET
and FETCH
clauses:
Note that you must use the
OFFSET
and FETCH
clauses with the ORDER BY
clause. Otherwise, you will get an error.
The
OFFSET
and FETCH
clauses are preferable for implementing the query paging solution than the TOP
clause.
The
OFFSET
and FETCH
clauses have been available since SQL Server 2012 (11.x) and later and Azure SQL Database.
SQL Server OFFSET
and FETCH
examples
We will use the
products
table from the samlpe database for the demonstration.
The following query returns all products from the
products
table and sorts the products by their list prices and names:
To skip the first 10 products and return the rest, you use the
OFFSET
clause as shown in the following statement:
To skip the first 10 products and select the next 10 products, you use both
OFFSET
and FETCH
clauses as follows:
To get the top 10 most expensive products you use both
OFFSET
and FETCH
clauses:
In this example, the
ORDER BY
clause sorts the products by their list prices in descending order. Then, the OFFSET
clause skips zero row and the FETCH
clause fetches the first 10 products from the list.
In this tutorial, you have learned how to use the SQL Server
OFFSET
FETCH
clauses the limit the number of rows returned by a query.
0 Comments