SQL Paging with Oracle, MySQL and MS SQL

I have to admit, I love databases. To hold data in a central place and be able to hook up any application from wherever you are to it, is just convenient to say the least. But there are moments I wish that vendors would “follow” a standard. I guess, when it came down to paging, every vendor thought his way of doing it is the best. No wonder, paging is the one SQL syntax that differs the most. Thus I dedicate, this post to paging and outlining the difference, meaning the solution to achieve it with Oracle, MySQL and MS SQL.

Paging with Oracle

Oracle knows of the internal column “ROWNUM”. Thus with “rownum” we can page trough the records, but have to do this with subselects. Look at the example;

SELECT rn, columns
   FROM (
	SELECT ROWNUM AS rn, columns
		FROM (
			SELECT columns
			FROM table
			WHERE id = 1
			AND something = 2
			ORDER BY columns
			)
		WHERE ROWNUM  0

In this example we select the first 10 records, if you want to select the next 10 records then you will need to increase the values for “ROWNUM” with 20 and for “rn” to 10.

Paging with MySQL

There is one thing I like MySQL, that is the “LIMIT” clause. Not only can you limit how many records to retrieve, but can also add a offset to the limit clause. Thus we can do very elegant paging with “LIMIT offset, number”. Again, we limit the query below to return us 10 records:

SELECT columns
FROM table
WHERE id = 1
AND something = 2
ORDER BY columns
LIMIT 0, 10

Now, this will return the first 10 records. So, what if we want to do a paging here? Actually, quite easy, all we have to do is to increase the offset by one. It is important to know, that the offset start with a “0” (think of it as the FIRST page), thus if we want to retrieve the next 10 records we use the limit with “LIMIT 1, 10”. This will give us the next 10 records. Elegant, no?

Paging with MS SQL

Now paging with MS SQL took me some time to figure out, since some said that MS SQL know of “rownumber” as well, but honestly, I couldn’t get anything to work with it. So the most easy method to do paging in MS SQL is to use the TOP syntax combined with a NOT IN subselect. Makes sense, right? Here is the example;

SELECT TOP 10 columns
FROM table
WHERE xxxx
AND id NOT IN (
	SELECT TOP 0 id
	FROM table
	WHERE xxxx
	)

As in the Oracle example above, if you would like to retrieve the next 10 records you would have to increase the TOP values by 10 records, meaning the FIRST TOP would have a value of 20 and the inner select TOP a value of 10.

So here you have it. How paging works for Oracle, MySQL and MS SQL.