SQL Paging with Oracle, MySQL and MS SQL

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.

Trusted by over 2,000 organizations with millions of emails

Helpmonks helps us answer customer emails at lighting speed
We couldn't do our job without Helpmonks!
Helpmonks, the best thing since sliced bread
Excellent option for collaborating email amongst your team
A good platform that is here to grow and help you grow
Helpmonks is a great tool that our company utilizes and depends on
Great shared inbox for the team
Fantastic email support service for any business
Easy to navigate and catalog data for specific users
Solid Helpdesk software w/ responsive customer support
A really good solution to manage customer support mailbox
Good support, useful features

One more thing...

Helpmonks gives you the ability tocollaborate as a groupin your shared mailbox, features aTeam To-Do App, powerfulCustomer Relationship Management (CRM)capabilities, a completeLive-Chat tool, a mighty Email Marketing Platform that includes sending ofemail campaignsandemail automation, collision detection, email tracking, reminders, mentions, custom fields, custom applications, customer satisfaction ratings, Single-Sign-On (SSO) & SAML authentication, Trello, Zapier, Slack, an API, and much more.

Check outthe complete feature listor sign up for afree 30-day shared inbox trial now