One of the thngs I like about my job is solving problems. In particular problems where there is a bit of thought involved. The particular problem I had is that for a search interface I’m building a requirement is the ability to page result set. This is easy using MySQL as it has a LIMIT keyword that limits the results to a certain number, and allows an offset, so for example you to retrieve results 16-20 you would use LIMIT 15,5.
Unfortunately, SQL Server does not have an equivalent to the offset argument. Its nearest is TOP n, which returns the first n rows.
This puzzled me for a few days, but then the answer came to me in a flash of inspiration: Results 20-16 would be the first 5 rows of a TOP 5 statement, with a nested query with a TOP 20 statement in the opposite order. Then I could just reverse the order to get the result the right way around.
So here’s what I came up with:
SELECT * FROM ( SELECT TOP x * FROM ( SELECT TOP y fields FROM table WHERE conditions ORDER BY table.field ASC) as foo ORDER by field DESC) as bar ORDER by field ASC
x is the number of rows you want returned and y is x+offset.
After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. For the queries I was running it was only about 2%.
Source: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/
