-2

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

Prithiv
  • 504
  • 5
  • 20
  • Is it mysql? Looks like SQL Server (mssql) – Jens Mar 28 '17 at 14:43
  • The query is complete wrong.`ORDER BY` clause always come after `WHERE` clause. `WHERE` does not have any condition in your query. What does it mean to have only `WHERE [ID]` ? – Chetan Mar 28 '17 at 14:57

2 Answers2

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.

Chetan
  • 6,711
  • 3
  • 22
  • 32
  • Hi @Chetan, Thanks for your suggestion. I have tried as you said, but still i get same exception. Is there any other possiblities for this case? – Prithiv Mar 28 '17 at 16:30
  • There is no other possibilities. It's a syntax error. I ran the above query on local db, only the table name changed, I get the perfect output. I ran this query on SQL Server database. Are you using some other database? Did you try running the query in Sql Management Studio. I suggest to copy the query from here, paste in query windows and run it. – Chetan Mar 29 '17 at 03:00
  • Hi @Chetan, Thanks . I didnot tried it with Sql Management Studio. I have used (.sdf) file in my project internally, and read the values from that by SqlCeConnection and SqlCeAdapter. – Prithiv Mar 29 '17 at 04:58
  • 1
    Ok. If you are using SQL Server Compact Edition then the news is ROW_NUMBER() is not available in SQL Server Compact Edition. You need to use `SqlCeResultSet.Seek()` for the paginatiion. – Chetan Mar 29 '17 at 05:23
  • Hi @Chetan, Thanks for the info. Would you provide any forum or documents for using SqlCeResultSet.Seek() for selecting the 100 records from SDF file. i have searched for that , but i can't get exact details about how to use SqlCeResultSet.Seek() – Prithiv Mar 29 '17 at 07:14
  • I mean that how to implement SqlCeResultSet.Seek() to my scenario (my previous query with ROW_NUMBER) – Prithiv Mar 29 '17 at 07:20
  • You can go thru https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.seek(v=vs.100).aspx – Chetan Mar 29 '17 at 11:14
0

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;
ErikEJ
  • 40,951
  • 5
  • 75
  • 115