I get a "Incorrect syntax near '4'." while executing this command :
@"SELECT * FROM [Table] OFFSET 4 LIMIT 2;"
what is the probem here?
Asked
Active
Viewed 1,079 times
-1
juergen d
- 201,996
- 37
- 293
- 362
David Neves
- 9
- 5
-
What DB engine do you use? – juergen d Sep 16 '17 at 19:49
-
Not sure how to get the version, but I'm using Visual studio express 2015 – David Neves Sep 16 '17 at 19:50
-
it may be MySQL or PostgreSQL – Ferdinand Gaspar Sep 16 '17 at 19:51
-
@FerdinandGaspar: It is SQL-Server. Look at the`[]` – juergen d Sep 16 '17 at 19:52
-
you cannot use LIMIT and OFFSET in SQL-Server. USE TOP instead – Ferdinand Gaspar Sep 16 '17 at 19:54
-
so how do I get N rows starting from a to b? – David Neves Sep 16 '17 at 19:56
-
Incorrect syntax near the keyword 'TOP'. – David Neves Sep 16 '17 at 19:57
2 Answers
0
The SQL SELECT TOP Clause The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.
SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
Example
SELECT TOP 2 *
FROM [Table]
Ferdinand Gaspar
- 2,043
- 1
- 8
- 17
0
There is no LIMIT in SQL Server. You use FETCH:
Without an ORDER BY, the OFFSET is meaningless, so you can just do
SELECT TOP 2 t.*
FROM [Table] t;
If you do have an ORDER BY:
SELECT t.*
FROM [Table] t
ORDER BY ?
OFFSET 4 ROWS FETCH FIRST 2 ROWS ONLY;
The ? is a placeholder for the name of the column you want to sort by.
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
-
Incorrect syntax near '?'. Invalid usage of the option FIRST in the FETCH statement. – David Neves Sep 16 '17 at 20:02
-
@"SELECT * FROM [Table] ORDER BY [ID] OFFSET 4 ROWS FETCH FIRST 2 ROWS ONLY;" – David Neves Sep 16 '17 at 20:09