SQL Server 'FETCH FIRST 1 ROWS ONLY' Invalid usage

Question

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

Answer 1

Try with OFFSET clause

select * from products.series where state = 'xxx' order by id 
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY

Answer 2

use top:

select top 1 * from products.series where state = 'xxx' order by id 

Answer 3

You can use top() function:

select top 1 * from table

Answer 4

SELECT TOP 1 * FROM (select * from products.series where state = 'xxx') as tmp ORDER BY id

Answer 5

QUESTION: ELSE, EXEC SQL SELECT A.CDE_TRAN_REF INTO :DCLTACTV-BASE-INTRA.CDE-TRAN-REF FROM (SELECT CDE_TRAN_REF FROM TACTV_BASE_INTRA WHERE NUM_OFFICE = :WS-SELECT-NUM-OFFICE
AND NUM_ACCT = :WS-SELECT-NUM-ACCT AND NUM_ACCT_TYPE = :WS-SELECT-NUM-ACCT-TYPE AND CDE_SRC_APPL_REF = :WS-SELECT-CDE-SRC-APPL-REF

               FETCH FIRST 1 ROW ONLY (ERROR HERE SAYING 'SELECT NOT VALID)
                UNION ALL 
                SELECT CDE_TRAN_REF
                FROM TACTV_BASE_INTRA_TEMP
                WHERE NUM_OFFICE = :WS-SELECT_NUM_OFFICE   
                     AND
                      NUM_ACCT = :WS-SELECT-NUM-ACCT
                     AND
                      NUM_ACCT_TYPE = :WS-SELECT-NUM-ACCT-TYPE
                     AND
                      CDE_SRC_APPL_REF = :WS-SELECT-CDE-SRC-APPL-REF
                    ) AS A
                 FETCH FIRST 1 ROW ONLY (COMPILES WITH NO ERRORS)
                 WITH UR 
            END-EXEC
           END-IF