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 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.
Now suppose you wanted to show results 11-20. With the
OFFSET keyword its just as easy, the following query will do:
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.
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.
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:
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
Like this? Follow me ↯Tweet Follow @pfreitag
SQL Pagination with LIMIT and OFFSET was first published on August 29, 2005.
If you like reading about sql, limit, offset, mysql, postgresql, or tips then you might also like:
- Returning TOP N Records
- Order by NULL Values in MySQL, Postgresql and SQL Server
- Top 10 Reserved SQL Keywords
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL
- SQL to Select a random row from a database table
- SQL Reserved Key Words Checker Tool
- Calculating Distance in Miles from Latitude and Longitude
- Backwards LIKE Statements