Menu
I am trying to convert a Db2 query to SQL Server, I came across a construct I am not familiar with: FETCH FIRST 1 ROWS ONLY.
This is the query working on db2:
select * from products.series where state = 'xxx' order by id
FETCH FIRST 1 ROWS ONLY
and the error I am getting on SQL Server:
Invalid usage of the option FIRST in the FETCH statement.
I have tried replacing FIRST with NEXT which seems to be admitted in SQL Server, but with no success.
I am using SQL Sever 2014
745
Offset in SQL is used to eliminate a set of records from a given table in a database in order to retrieve a set of records according to the requirement of the database. Basically, it is used to find a starting point to display a set of rows as a final output.
OFFSET and FETCH Clause are used in conjunction with SELECT and ORDER BY clause to provide a means to retrieve a range of records. OFFSET. The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records.
When OFFSET is 0, then no rows are skipped. If OFFSET is greater than the number of rows in the ordered results, then no rows are returned.
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table.
Try with OFFSET clause
select * from products.series where state = 'xxx' order by id
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY
use top:
select top 1 * from products.series where state = 'xxx' order by id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
