SQL Pagination with LIMIT and OFFSET

Both MySQL and PostgreSQL support a really cool feature called OFFSET that is usually used with a LIMIT clause.

The LIMIT clause is used to limit the number of results returned in a SQL statement. So if you have 1000 rows in a table, but only want to return the first 10, you would do something like this:

SQL To Return First 10 Rows

SELECT column FROM table
LIMIT 10

This is similar to the TOP clause on Microsoft SQL Server. However the LIMIT clause always goes at the end of the query on MySQL, and PostgreSQL. The limit and offset statement goes after the where clause, after group by, and after order by statements.

Now suppose you wanted to show the second page, results 11-20. With the OFFSET keyword its just as easy, the following query will give the results for page two:

SQL Pagination (return rows 11 through 20)

SELECT column FROM table
LIMIT 10 OFFSET 10

This makes it easy to code multi page results or pagination with SQL. Often the approach used is to SELECT all the records, and then filter through them on the application server tier, rather than directly on the database. As you would imagine doing this on the database yields much better performance.

Always use ORDER BY

It is important to point out that you should always be using an ORDER BY statement when you are using LIMIT and OFFSET. Using the ORDER BY ensures that you get a consistent set of pages. I have even heard of some cases where using a DISTINCT in the query can cause the same result set to show up no matter what the OFFSET value is.

SELECT column FROM table
ORDER BY id DESC
LIMIT 10 OFFSET 10

Calculating the OFFSET from Page Numbers

There are two different ways I have coded pagination in SQL, either I pass a limit and offset directly as variables, or I provide the page number as a variable. The page number approach is probably a bit more secure as then someone can't make a page that returns all the rows in your table by manipulating the input variables. When you only allow the page number variable, you can keep control of how many items to show on each page.

Suppose you want page 3 of your SQL result, in that case you want LIMIT 10 OFFSET 20, here is some psudo code to calculate the page offset:

limit = 10;
offset = (limit * pageNumber) - limit;

SQL Pagination for MySQL and PostgreSQL

I have known that PostgreSQL supports the OFFSET keyword for quite some time, and for some reason I always thought it was not supported by MySQL. Well it turns out that it is supported now. It is nice to be able to use the same syntax for SQL pagination on both MySQL and PostgreSQL.

I've worked with many different database platforms and I think that MySQL and PostgreSQL have the cleanest way to do SQL pagination with LIMIT and OFFSET