SQL: Limiting Queried Rows

The Problem
Where I work, we have a few databases with some pretty large tables. Record counts into the hundreds of thousands are a dime a dozen, and you'll even encounter the occasional table with multiple millions or records. Though I dearly love the SQL keyword TOP, sometimes it is inadequate. As a web programmer, how often have I wished that there were a way to tell a SQL Server to only return rows 50 to 100? Doing so would make result pagination a breeze.

I posted a question to a Cold Fusion Message Board that I frequently visit, and my main man Lance M. (http://www.webasics.net/) came to the rescue by posting three possible solutions that he had read about in a SQL book.

The Solution
While there doesn't really appear to be a way to specify which results to return short of using a stored procedure, there are several possible workarounds. The workaround that I liked best is both simple and elegant. I will post it here. If you are dieing (spelling?) to know the other two, make a comment, and I will post them as well

If you want to get records 1-50 from the fictitious table "author" you would, of course, just do the following:

SELECT TOP 50 *
FROM author
ORDER BY last_name;

However, if you wanted to get records 51-100, you can do this:

---------------------------
SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC;
---------------------------

Presto! What you end up with is records 51-100 (albeit in reverse alphabetical order). This query essentially gets the first hundred authors, reverse alphabetizes them (ORDER BY last_name DESC), and then returns the first 50 from the upside down result. Granted, this method is not as efficient as just being able to specify a STARTROW and an ENDROW, but it is a LOT more efficient than having the database return a coupl'a hundred thousand records, and then just picking out the chunk of 50 that you want.

If you want to re-alphabetize the result, just add the following wrapper:

---------------------------
SELECT *
FROM
(SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC)
ORDER BY last_name ASC;
----------------------------

Very cool, Lance. Thanks for the tip.