CODEWITHSUNDEEP
sql-serverlimit
dcparham
dcparham

Reputation: 291

SQL Server LIMIT 1 and LIMIT 1,1 syntax error

Interestingly enough I found no post for this specific, but basic issue.

Goal: update the latest budgetid record docstatus = 0. Then I want to update the next-to-last budgetid record docstatus = 1. I am trying this within PHP but also testing in my SQL Server SEM and it is failing there, too.

My SQL Server statement: 

select 
    budgetid, docstatus, datechanged 
from 
    ccy_budget 
where 
    activityid = 11111 
order by 
    datechanged desc 
limit 1,1;

Error that occurs in SEM is:

Incorrect syntax near 'limit'.

Yet in w3schools this [sample] sql works just fine:

SELECT * 
FROM Customers 
ORDER BY postalcode DESC 
LIMIT 1,1;

Seems so simple, surely I am missing something fundamental.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Upvotes: 1

Views: 8287

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28930

Equivalent syntax in SQL Server would be 

select * 
from table
order by somerow desc
offset 1 rows fetch next 1 rows only;

But the above is available from SQL Server 2012 on, so for your version, you have to some thing like below

;with cte
as
(
select *,row_number() over (order by postalcode desc) as rn
from table
)
select * from cte where rn=2

Upvotes: 4

Related Questions