question

William Brewer avatar image
William Brewer asked

What is the fastest way to get the x th block of y rows of an ordered result?

When a front-end application is using a scrolling listbox, one gets calls to the database for a result consisting of, say, the fourth block of 20 rows of a result bases, perhaps, on date order. If you are supplying a list of customers, one might want to page the list based on the user clicking on the scroll bar. Conventional wisdom has it that one uses the 'Top and Tail' approach, where one gets the result up to the end of the page you want, and then reverse the order before reading just that page you want, both time using TOP to get the numbers you want in the result. Then you end by ordering the page as the user-interface wants...

There must be a quicker way now based on one of the SQL Server 2005 Row-numbering techniques. If so, what is it? (I haven't been asked to do this by the Devs for a while!)

t-sqlrow_number
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Matt Whitfield avatar image
Matt Whitfield answered

The basic construct is as follows:

SELECT * FROM 
  (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as __RN FROM Table) iDat
WHERE __RN BETWEEN ((@pageNumber - 1) * @rowsPerPage) + 1 and (@pageNumber * @rowsPerPage)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.