One of the features that MS SQL Server 2000 is sorely lacking is the ability to select rows 10-20 or 11-20, or whatever range you want to select. In MySQL, you can just do a ” LIMIT 5, 10 ” , and it will pull the first 10 rows after the 5th row. This is very useful for paging through a set of items, for instance. SQL Server does provide a “SELECT TOP” feature, but you can’t set an offset to start from.

I came across a solution on Google Groups today, and it was so useful I just had to post about it… mostly so I’ll remember it. I’ve always used a stored procedure with derived tables or temporary tables to accomplish this in SQL Server, but here’s the simple way around it:

SELECT TOP 10 *
FROM (SELECT TOP 14 * FROM MyTable ORDER BY OrderColumn) as T
ORDER BY OrderColumn DESC

This gives you the bottom 10 of the top 14, which is the same thing as a MySQL ” SELECT * from MyTable LIMIT 5, 10″. If you wanted to retrieve the next 10 records, you would simply do this:

SELECT TOP 10 *
FROM (SELECT TOP 24 * FROM MyTable ORDER BY OrderColumn) as T
ORDER BY OrderColumn DESC

Obviously this is now selecting a larger amount of records on the second pass…  and each time you page further you’d be selecting another 10 records…  but this could still be very useful. Note that this method only works if you are sorting your results. Of course you should be doing that anyway, right ?