SQL Server Error, "Invalid usage of the option FIRST in the FETCH statement."
SQL Server has implemented the OFFSET
and FETCH
clauses as part of the ORDER BY
clause, as pointed by the other answers and documented in their documentation.
The SQL standard on the other side, has both of these clauses as independent:
<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
If someone wants this feature implemented in full compliance with the standard, they can always make a request to the SQL Server team, through the Connect channel. In fact, MS has commented - in a different request about offset and fetch:
Connect item: SQL Denali: add total rows counter to
SELECT
statement - by Alexey RokhinAnswer: Posted by Microsoft on 24/11/2010 at 11:34
The requirement that
OFFSET/FETCH
requiresORDER BY
is a restriction in this release. In the ANSI SQL standard (SQL:2011) where the newOFFSET/FETCH
clauses are proposed,ORDER BY
is optional. The restriction in SQL Server has to do with limitation in our parser technology that cannot handle the optional syntax without makingOFFSET
a reserved keyword. We may remove it in the future.Now with respect to ...
Until then, if one wants to use OFFSET
and FETCH
without a specific ORDER BY
, a workaround is to add a "do nothing" order by clause. Example:
SELECT
...
ORDER BY (SELECT NULL)
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;
As stated at the very top of the documentation on OFFSET
..FETCH
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
...
ORDER BY is mandatory to use OFFSET and FETCH clause.
So,
SELECT *
FROM ( VALUES (1),(2),(3) ) AS t(x)
ORDER BY t.[x] /* <-- ADD ME TO BE HAPPY */
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;
Not all that practical for a simple LIMIT
if that's what you're going for you'll want to stick with TOP
.
According to the reference, the OFFSET
clause is part of ORDER BY
in SQL Server. You'll also need to add the ROWS
keyword after the OFFSET
specification:
SELECT *
FROM ( VALUES (1),(2),(3) ) AS t(x)
ORDER BY x
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY;