SQL Server Compact Code Snippet of the Week #4 : select rows 50 to 60 from a table

For many scenarios it can be useful to be able to get only a subset of rows fro a long result set. SQL Server Compact version 4.0 adds support for the OFFSET – FETCH keywords, that allows you to retrieve a single “page” of data. Sadly, this is not possible with version 3.5. Notice, that this syntax is supported on the SQL Server Compact version used on Windows Phone, despite the 3.5 label of that runtime.

For 4.0 you can use:

SELECT [Order ID]
,[Product ID]
,[Unit Price]
,[Quantity]
,[Discount]
FROM [Order Details]
ORDER BY [Order Id]
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY
GO

This statement returns only 10 rows.

For 3.5, you can only use TOP, and must then skip the rows that you do not need:

SELECT TOP (60) [Order ID]
,[Product ID]
,[Unit Price]
,[Quantity]
,[Discount]
FROM [Order Details]
ORDER BY [Order Id]
GO

This statement returns 60 rows, and you must manually skip the first 50.