Paging In SQL

Paging is the term to describe getting a sub selection of data from a record set. Imagine you have a list of 1000 records but you don’t want your user to have to deal with 1000 all at the same time so you break it up into pages. You set a system limit and say they can only have 50 or 100 at a time and therefore have to browse through the various pages to find something.

The most common practical online use of this is forum software. Instead of showing you all 9billion replies to a topic, it’ll only show you 10 at a time… Why? A. the user only has to download the last page if they want to see updates on the thread (or just want to see the first page, etc) and B. That means the host only has to upload that much at a time. 99 times out of 100, this equates to everyone saving bandwidth and decreasing page load times.

So lets look at the SQL for a record set’s generation

SELECT * FROM table_name;

That will get us all the data from table_name. If we want to get the first 30 records of that table, we need to start using database-specific syntax (meaning the same thing wont work for MS SQL Server as for MySQL). The MySQL is:

SELECT *  FROM table_name LIMIT 30;

And the MS SQL server version is:

SELECT TOP 30 * FROM table_name;

LIMIT, as you’re about to find out is an amazing keyword. There are two versions of it… One that gets the first <insert number here> records and another which gets a range. So now… What if we want to get the third "page" of this data… Records 61 to 90. Using LIMIT, MySQL’s looks like this:

SELECT * FROM table_name LIMIT 60, 30;

Incase you’re wondering the syntax is:

LIMIT <offset>, <rowcount> ;

So that makes it extremely easy. MS SQL is a completely different story. As a user of MySQL, MS SQL and Postgre, I can honestly say this is possible the biggest hole in MS SQL… Its something they should have fixed years ago… But on with the code…

SELECT *
FROM (
	SELECT TOP 30 *
	FROM (
		SELECT TOP 90 * 
		FROM table_name
	) AS internal 
	ORDER BY internal.ID DESC
) AS external
ORDER BY external.ID ASC;

I warned you it was going to be ugly. So lets review what this actually does. The most internal query looks the same as the original query except that its getting all the records up to the end of the page that we want. The second query then reverses the order (so the data IDs are 90, 89, 88, 87, etc) and grabs the top 30. The most external query then flips it back over so its in the same order as it started. Oracle has its own special way of dealing with paging (using a quite intuitive ROWNUM variable that you can WHERE against… So you can say:

SELECT * FROM table_name WHERE ROWNUM > 60 AND ROWNUM <= 9;

PostgreSQL uses yet another way (which MySQL has since incorporated into their syntax if you want to use it):

SELECT * FROM table_name LIMIT 30 OFFSET 60;

If you’re making a system that you might want running the same SQL on different databases, you’re going to have to think long and hard over how you do your data-paging. In that sort of environment, it might be easier to query the database for all the IDs and manually work out where the ranges are and do WHERE commands to get your IDs. I hope this helps someone.