Variation on Select top n

Question

Is is possible to do a variation of select top n rows to select top n rows starting at a row other than 0. My (mobile) app has limited resources and no server side caching available. The maximum rows returned is 100. I get the first 100 by select top 100. I would then like the user to be then able to request rows 101-200 and so on. The database data is static and the the re-query time negligible. Platform SQL Server 2008

Answer 1

Here's an article which demonstrates such queries using the ROW_NUMBER function.

Answer 2

;With CTETable AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY Column_Name DESC) AS ROW_NUM, * FROM TABLENAME WHERE <CONDITION>
)

SELECT Column_List FROM CTETable WHERE ROWN_NUM BETWEEN <StartNum> AND <EndNum>

Use your [startNum] and [EndNum] to be any series you want maybe 123 - 147 ! This will work well !