Invalid query syntax

Question

I have a query like so: SELECT TOP 20 a.Job_No FROM dbo.ScheduledatesFF Which gives me the first 20 results, but I want to start at 20 and get 20 results, how do I set an Offset?

I tried: OFFSET 20 at the end of the query and I got this error:

Incorrect syntax near 'OFFSET'

I know in mysql its Offset, is it something different in SQL?

This gives me an error:

SELECT 
    a.Job_No 
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY a.Job_No) as RowNum 
    FROM dbo.ScheduledatesFF 
    FROM dbo.ScheduledatesFF AS a 
    INNER JOIN dbo.tblCustomers AS c 
        ON a.Job_No = c.Job_No 
    INNER JOIN dbo.scheduledatesSS AS z 
        ON a.Job_No = z.Job_No 
    LEFT OUTER JOIN dbo.maxscheddate AS m 
        ON a.Job_No = m.Job_No
        ) AS MyDerivedTable 
WHERE MyDerivedTable.RowNum BETWEEN 0 AND 20

Incorrect syntax near the keyword 'FROM'.

This is the closest I have come:

SELECT a.Job_No, ROW_NUMBER() OVER (ORDER BY a.Job_No) as datacounter FROM dbo.ScheduledatesFF AS a INNER JOIN dbo.tblCustomers AS c ON a.Job_No = c.Job_No INNER JOIN dbo.scheduledatesSS AS z ON a.Job_No = z.Job_No LEFT OUTER JOIN dbo.maxscheddate AS m ON a.Job_No = m.Job_No

but in the where clause for datacounter it says it does not exists

Did you look at [the documentation](http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx)? Are you running SQL Server 2012 or better? — Aaron Bertrand, Aug 12 '14 at 19:30
This question appears to be off-topic because this covers fictitious syntax — billinkc, Aug 12 '14 at 19:35
Maybe try starting with something simpler. Right now you have `FROM dbo.ScheduledatesFF FROM dbo.ScheduledatesFF` which would fail regardless of any of this paging syntax you're trying. — Aaron Bertrand, Aug 12 '14 at 19:52
Something that you might consider is identifying a format for your SQL code that you can accept and then applying that consistently. You can observe from those that have edited your question, it's nearly impossible to parse, much less comprehend issues when it scrolls for several pages. — billinkc, Aug 12 '14 at 19:58
@AaronBertrand MySQL has option for LIMIT syntax with OFFSET. Either `LIMIT row_count` (0 offset) or `LIMIT offset, row_count` or `LIMIT row_count OFFSET offset` — ypercubeᵀᴹ, Aug 13 '14 at 08:59
@ypercube OK, still, guessing at syntax and refusing to read the documentation and throwing hands in the air when it doesn't work... — Aaron Bertrand, Aug 13 '14 at 13:27

score 3 · Answer 1 · edited Aug 12 '14 at 20:31

3

Looking at the code you've posted your problem is simple. you have two "FROM dbo.ScheduledatesFF"'s

See.

SELECT 
    MyDerivedTable.Job_No 
FROM (
    SELECT 
        a.Jon_No,
        ROW_NUMBER() OVER (ORDER BY a.Job_No) as RowNum 
    FROM dbo.ScheduledatesFF --<--What's up with this guy here?
    FROM dbo.ScheduledatesFF AS a 
    INNER JOIN dbo.tblCustomers AS c 
        ON a.Job_No = c.Job_No 
    INNER JOIN dbo.scheduledatesSS AS z 
        ON a.Job_No = z.Job_No 
    LEFT OUTER JOIN dbo.maxscheddate AS m 
        ON a.Job_No = m.Job_No
        ) AS MyDerivedTable 
WHERE MyDerivedTable.RowNum BETWEEN 0 AND 20

Still Think the below solution is superior but this is why you're getting that syntax error.

edited Aug 12 '14 at 20:31

Lamak

69,480
12
108
116

answered Aug 12 '14 at 19:51

Zane

4,129
1
21
35

Removing the extra from, gives me a new error 'a.Job_No' could not be bound. State 1, Line 1 – user3922763 Aug 12 '14 at 19:56
1

@user3922763 jeezus, change your outer `SELECT` to `SELECT MyDerivedTable.Job_No` and please try to read the error messages to debug your code – Lamak Aug 12 '14 at 20:02
@user3922763 Please go to [code golf](http://codegolf.stackexchange.com/) or something. You need to do some debugging yourself. – Aaron Bertrand Aug 12 '14 at 20:06
You removed the as a one I'm assuming. – Zane Aug 12 '14 at 20:07
1

@Zane the problem is with the outer `SELECT` – Lamak Aug 12 '14 at 20:08

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

take a look here: Row Offset in SQL Server It looks as though there's no direct equivalent keyword, however the same effect can still be achieved via a little bit of a workaround.

SELECT Job_No
FROM (
SELECT Job_No, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

replace @startRow and @endRow with your starting and ending row numbers.

Update: Basing on updated info in the question:

 SELECT 
 Job_No 
 FROM (
 SELECT 
 ROW_NUMBER() OVER (ORDER BY a.Job_No) as RowNum 
 FROM dbo.ScheduledatesFF AS a 
 INNER JOIN dbo.tblCustomers AS c 
 ON a.Job_No = c.Job_No 
 INNER JOIN dbo.scheduledatesSS AS z 
 ON a.Job_No = z.Job_No 
 LEFT OUTER JOIN dbo.maxscheddate AS m 
 ON a.Job_No = m.Job_No
 ) AS MyDerivedTable 
 WHERE MyDerivedTable.RowNum BETWEEN 0 AND 20

That's my best guess anyways. It looks like you had 2 from statements back to back....

edited May 23 '17 at 10:33

Community

1
1

answered Aug 12 '14 at 19:28

user2366842

1,231
14
23

I do not understand that syntax – user3922763 Aug 12 '14 at 19:30
1

SQL Server 2012 or better [has much better syntax](http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx). – Aaron Bertrand Aug 12 '14 at 19:33
True, I was going for a safer solution, as version used wasn't stated....@user3922763 answer updated some for clarification. – user2366842 Aug 12 '14 at 19:35
Is there another way to go about this? – user3922763 Aug 12 '14 at 19:36
Depends on the version of SQL Server that's being used. If you're on 2012 or newer, go with the link that @AaronBertrand provided, if not, then unfortunately no. – user2366842 Aug 12 '14 at 19:38
2008...I have updated my question, with I have tried and what error I am getting – user3922763 Aug 12 '14 at 19:45
Please use the code button {} to format blocks of code. Backticks are for inline words here and there. – Aaron Bertrand Aug 12 '14 at 20:05
Fair enough. Bad habits die hard. – user2366842 Aug 12 '14 at 20:07
1

Not big on indenting for readability, either, I take it. Anyway your code still has an error - the outer column looks for `a.Job_No` but the derived table is not called `a`. Usually setting up a [SQL fiddle](http://sqlfiddle.com/#!3) prevents these errors caused by untested code. – Aaron Bertrand Aug 12 '14 at 20:11

Invalid query syntax

2 Answers2