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

Sueannsuede

Answer 1

Try with OFFSET clause

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

Dandy

Just be aware that this syntax was introduced with SQL Server 2012, and cannot be used with earlier versions (like SQL Server 2008 or SQL Server 2008 R2). Just a caveat for those reading along. The "TOP" solution works with all versions of SQL Server. – Explanatory

Also note that "order by" is mandatory here. Trying to use offset and fetch without order by fails on SQL Server. SQL is probably the most portable query language around, but that really doesn't say much. – Syllabogram

Answer 2

use top:

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

Findlay

I imagine using TOP gives the same result but if possible I prefer to keep the syntax as similar as possible to the db2 query – Sueannsuede

Answer 3

You can use top() function:

select top 1 * from table

Wearable

Answer 4

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

Daviddavida

Please always post some text to explain why it works – Millsap

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

Repetitious

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Bushire