Paginating Results with MS SQL

by Curtiss   |   last updated August 4, 2009   |   PHP Tutorials ·

In my last post, I showed you just how simple it is to retrieve paginated results using MySQL. Unfortunately, MS SQL doesn’t utilize the LIMIT command and doesn’t implement any easy way to retrieve paginated results. Instead, you actually have to run three nested queries with MS SQL in order to retrieve a specific subset of results. MS SQL supports a clause called “TOP” rather than “LIMIT” and the “TOP” clause only accepts a single parameter (the number of results to retrieve).

To retrieve paginated results with MS SQL, you will need to write the same query three times, reversing the ORDER BY clause in each query. You also have to use a little simple math in your queries.

Let’s start with the inner query. This query should pull the larger recordset from which you will paginate. Let’s say you want to show 250 results, starting with the 1,251st result. Your inner query will look like:

SELECT TOP 1500 * FROM tbl ORDER BY id;

Next, you need to write a query that retrieves the last 250 results from that inner query. To do that, you need to select the “TOP” 250, from the first query, and reverse the order of your results. That query might look like:

SELECT TOP 250 * FROM (SELECT TOP 1500 * FROM tbl ORDER BY id) AS nt ORDER BY id DESC;

Finally, you need to reverse the order of those results so that they are ordered correctly again. Altogether, your three queries would look something like:

SELECT * FROM (SELECT TOP 250 * FROM (SELECT TOP 1500 * FROM tbl ORDER BY id) AS nt ORDER BY id DESC) AS nt2 ORDER BY id;

I believe, in order to get things working properly, you will need to alias each of your nested queries (which is why you see “AS nt” and “AS nt2” in the queries above).

It then gets even more complicated when you need to order your results by more than one column. For instance, if you want sort first by “lastname”, then by “id”, you will need to reverse the direction of “lastname” but not “id” in your second query. Therefore, your full query might look something like:

SELECT * FROM (SELECT TOP 250 * FROM (SELECT TOP 1500 * FROM tbl ORDER BY lastname, id) AS nt ORDER BY lastname DESC, id) AS nt2 ORDER BY lastname, id;

Finally, another hiccup in using this method is that you need to know how many total records would be retrieved by your innermost query if you didn’t use the “TOP” clause. If you try to retrieve the TOP 1500 results, but there are only 1,350 results in the recordset, then you’ll end up with records 1,101-1,350 instead of 1,251-1,350. Therefore, you should use a “COUNT” query to figure out how many results you’re going to retrieve first, then use some simple math to figure out what your “TOP” clause should look like. Here is some PHP code that might help:

$retrievehm = (($start+$howmany) <= $total) ? $howmany : ($total-$start);

In the example above, “retrievehm” is the variable I’m going to plug into my innermost “TOP” clause, “start” is the first result I should return (in the main example, that would be 1,250), “howmany” is how many results I want to display on the page (in the main example, that’s 250) and “total” is the total number of records that could be returned without the “TOP” clause. If “start” plus “howmany” is less than or equal to the total number of records, then we tell our second query to retrieve “howmany” (in this case, 250) results. If not, we subtract the “start” (in this case, 1,250) from the total number of records, and tell our second query to retrieve that number of results. With these variables in mind, our query might look like:

SELECT * FROM (SELECT TOP $retrievehm * FROM (SELECT TOP $start+$howmany * FROM tbl ORDER BY lastname, id) AS nt ORDER BY lastname DESC, id) AS nt2  ORDER BY lastname, id;

I realize this can be extremely confusing. I hope this post helps shed a little light on the subject. As a parting note, I would also like to remind you that, with MS SQL, if you are going to use the GROUP BY clause for any reason, you have to specifically name each column you want to retrieve from your table in the “SELECT” portion of the query, then you have to list all of those columns in the GROUP BY clause, as well.