CODEWITHSUNDEEP
sql
user7729132
user7729132

Reputation:

Can this query be modified to include an offset?

Currently using an Excel form with some basic VBA to pass data between end users and a SQL table. I'm running into a bit of a problem though, where the users may wish to skip the record they're being presented, and get the next one. Because I've got 3 users (currently) sharing the data, I need to be sure Person A and Person B aren't conflicting each other. I do this by updating a status field to "LOCKED" as soon as User A receives her data.

Anyway, I've been trying to figure out how the OFFSET works, but am not having much luck.

The abridged version of the statement:

Select top 1 ID,Query,Status from mydb.mytable where status = 'NEW' order by Priority desc

That gives me the highest priority record in NEW status. A second statement runs to update "NEW" to "LOCKED"

But if the user can't handle the record, she needs a different one. I thought something like the below would work, but I just can't get it to work.

Select top 1 ID,Query,Status from mydb.mytable where status = 'NEW' order by Priority desc offset 5;

Thank you for any help!

Upvotes: 3

Views: 6412

Answers (1)

chaosifier
chaosifier

Reputation: 2964

According to MSDN :

TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope).

Assuming you're using SQL Server 2012 or above, this is what you can do instead.

SELECT ID, Query, Status FROM mydb.mytable 
WHERE status = 'NEW' 
ORDER BY Priority DESC
OFFSET 5 ROWS
FETCH NEXT 1 ROW ONLY;

The FETCH NEXT 1 ROW ONLY will give you the very first row after offset, which is equivalent to what you'd get by doing TOP 1.

Upvotes: 8

Related Questions