There was an error parsing the query. [ Token line number = 1,Token line offset = 39,Token in error = ( ]

Question

I'm working in WinForms application. My requirement is loading the data from sql on demand(i.e load 100 records for a page, when moves to that page). So i tried below SqlCommand but it throws the exception at place of "ROW_NUMBER()" syntax in the below command,

SELECT * 
FROM (SELECT *
      , ROW_NUMBER() (ORDER BY [ID]) AS RowNum 
      FROM [tblVGTest] 
      WHERE [ID]) AS Temp 
WHERE RowNum BETWEEN 0 AND 100

Please let me know, is there any mistakes in command or provide any suggestion for my scenario.

Thanks

Answer 1

You were forgetting using OVER() clause with ROW_NUMBER.

Try following Query.

SELECT * FROM (SELECT * , ROW_NUMBER() OVER (ORDER BY [ID]) AS RowNum 
  FROM [tblVGTest] ) AS Temp WHERE RowNum BETWEEN 0 AND 100

I have removed WHERE clause from it as it was not having any criteria. You can put if it's required for you.

Answer 2

If you use SQL Server Compact 4.0, you can use the OFFSET / FECTCH syntax:

SELECT * FROM TransactionHistory   
ORDER BY TransactionDate DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;