LIMIT and OFFSET SQL Pagination

August 29, 2005

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:

SELECT column FROM table

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:

SELECT column FROM table

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.

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.

Like this? Follow me ↯

You might also like:

87 people found this page useful, what do you think?


MySQL has had OFFSET for as long as I've used it (back to the 3.23 days). I was actually wondering about this the other day: how do you get page N of a result set in MS SQL Server? Oracle has ROWNUM to get this effect, correct?
Thanks Michael, I'm aware of that one. However I like to use OFFSET because I think it is more readable, and is compatable with PostgreSQL syntax.
Good question Barney. I don't use SQL Server much anymore myself. But perhaps we have some SQL Server gurus reading this that can answer that. Yes on Oracle you can use ROWNUM to do this, eg: SELECT column FROM table WHERE ROWNUM BETWEEN 10 AND 20 Yes your right OFFSET has been in there for a long time, I think I read once that OFFSET was only supported by PostgreSQL, so I never checked MySQL. Either way, I'm sure lots of folks don't know about it.
I can't speak for Oracle, but in MySQL it's SELECT .. WHERE .. ORDER .. LIMIT, and the LIMIT only applies to the rows that match the ORDER BY clause. I'd imagine Oracle behaves the same, even though the order doesn't seem to indicate that behaviour, because ROWNUM only makes sense in relation to the sorted result set.
One more thing to mention. If you add "SQL_CALC_FOUND_ROWS" just after the SELECT keyword, you'll lose some of the performance improvements that LIMIT can bring, but MySQL will compute the total number of rows that would have been returned by the query if the LIMIT clause hadn't been present. The value value can be retrieved with a subsequent query via the FOUND_ROWS() function. Note that like LAST_INSERT_ID(), FOUND_ROWS() is a per-connection thing, which means you HAVE to be inside a transaction on BD for it to work correctly.
Thanks for pointing out the FOUND_ROWS() function Barney, I had read about it but didn't test it.
Hi Pete, I found this page looking for info on pagination using php and MySQL. Can you recommend any books or Web sites that deal with this in depth? Many thanks in advance, -Bob
Hi Bob, I don't use PHP much, so I can't really recommend anything specifically for that. In general however, you need to check the FOUND_ROWS() as barney mentions to get the total number of results. And also keep a URL variable for the offset. Then you just need a bit of logic for your next page button, and previous page button. IF the offset variable is at 0 (first page), then don't display previous pages, otherwise subtract limit from offset, and use that in the link for previous. For next page, display the link if FOUND_ROWS() is greater than offset+limit, the offset for the link to the next page is going to be offset+limit. Hope that helps you out bob. -pete
Thanks for the reply Pete. You mentioned "And also keep a URL variable for the offset." Can you elaborate on this or perhaps post a short example? (I'm not sure what that is). Thanks again, -Bob
Sure, I'll elaborate a bit: Your url might be something like this /search.cfm?query=chickens&offset=10 that would be the url for page 2. For page three the url might be: /search.cfm?query=chickens&offset=20 So your query will have something like: SELECT stuff from animals WHERE animal LIKE '%#url.query#%' LIMIT 10 OFFSET #url.offset# Ofcourse you need to make sure that the offset variable is indeed an integer, else you can get hacked with SQL injection hacking.
Do not used the OFFSET syntax with MySQL because it will not return the same results as LIMIT with the comma seperator. Insert 30 rows into a table, now do this: SELECT ... LIMIT 0, 10; You get the first 10. Try this: SELECT ... LIMIT 0 OFFSET 10; You get 0. Don't use it.
Matt, you're using OFFSET wrong. In the first example, you want 10 rows, skipping the first zero, in your second example, you want zero rows, skipping the first 10. In other words, when you use OFFSET, the numbers are reversed to when you use a comma.
Sorry to contradict some here but, on oracle, the simple SELECT col1 FROM table WHERE rownum BETWEEN 10 AND 20 won't work. in fact, if you really want to get pagination AND order in a single query on oracle, you have to use the following (this is an example, there are many variants, but it is on of the most simple) : SELECT col1 FROM (SELECT col1, ROWNUM r FROM (SELECT col1 FROM table ORDER BY col1)) WHERE r BETWEEN You need three nested selects because : - The rownum is calculated based on the id of each returned row. As long as there are none, ROWNUM stays at 0. Therefor, you need to compute the rownum of each row before sorting some out. - The rownum is computed before the ORDER BY. If you used only two nested selects and did the order and the rownum computing in the same one, it would be as if no sort had been done (from the outer select perspective). The conclusion of all that is that pagination on oracle can, under certain circumtances, be considers a mistake. In fact, it might, in some cases (in J2EE for instance), prove more efficient than other available solutions).
hi also love to here about this in case of sql server. Top clause is very short, but it seams we have no sql server guru passing by :(
I'm a SQL Server newb but this is what I came up with (requires an autonumber column to work): Select TOP 20 * From [Table] WHERE ID > (SELECT TOP 1 x FROM (SELECT TOP 1000 ID as x FROM [Table] ORDER BY ID) as x ORDER BY x DESC)
>>>select first 20 records<<< select * from (SELECT fields,rownum r FROM table ) where r between 0 and 20
hi all.... I have an solution for the pagination in oracle. its using ROWNUM with the use of ROWID ROWID can be used as the offset and the ROWNUM can be use as the limit. With ordered_city as { select * from city Order by city.rowid } select * from ordered_city ord_city where "yourrowid" < city.rowid and rownum < 100; this will fech 100 records starting from yourrowid. Rowid is an unique value for each row in oracle.
NOT, Not, not There are many ways to do pagination like XML, or Cursors etc. but this is the unique stable way to do a good pagination using ROWNUM in oracle becouse the use of agregate functions like distinct, rank and ROWNUM of the incorrect subselect are inestable. I hope This Function Work for you its partially the same but have 2 subselects instead of 1: select * from (select col1, col2..., rownum rn (SELECT distinct col1, col2... FROM table1, table2 WHERE col1=... GROUP... ORDER BY... HAVING...)) --THER GO THE PAGINATION Where rn between rowI and rowJ
AND other way for not passing rn column in the sql statement is: select * from (select * from (SELECT distinct col1, col2... FROM table1, table2 WHERE col1=... GROUP... ORDER BY... HAVING...)) --THER GO THE PAGINATION Where rownum between rowI and rowJ
To do OFFSET and LIMIT with SQL Server, see here: Instructions for - SQL Query - Stored Procedure - User-Specific Records - Application-Wide Records
in Oracle there is an analytical function row_number) which will get rid of the need for the third subquery. The syntax is a little strange and will take some getting used to but you can do the following: select {your column list} from (select row_number() over (order by {your order by statement}) row_order, {your column list} from {your table}) where row_order between 11 and 20 There are some additional advantages to row_number(). For one you can use partitioning. Say you wanted to pull the top five salaries for each department. If you partition over the department and order by the salary descending you can get numbers 1 through five for each department. (this will mess with your pagination though and you should not use the same row_number() result for both) That brings me to the second cool thing that you can use row_number() for. You can actually grab multiple ordering results for multiple reasons in one subquery. Think fantasy football for a minute. You want a listing of players in the top five of passing yards, rushing yards, and catching yards. You can use one query to get them all. Here is my example: select player_name, passing_yards, rushing_yards, catching_yards from (select row_number() over (order by passing_yards desc) passing_rank, row_number() over (order by rushing_yards desc) rushing_rank, row_number() over (order by catching_yards desc) catching_rank, player_name, passing_yards, rushing_yards, catching_yards from player_stats) where passing_rank < 6 or rushing_rank < 6 or catching_rank < 6 order by player_name; using partitioning you can do the same thing in one query for the top five for each stat in AFC vs NFC. select conference, player_name, passing_yards, rushing_yards, catching_yards from (select row_number() over (partition by conference order by passing_yards desc) passing_rank, row_number() over (partition by conference order by rushing_yards desc) rushing_rank, row_number() over (partition by conference order by catching_yards desc) catching_rank, conference, player_name, passing_yards, rushing_yards, catching_yards from player_stats) where passing_rank < 6 or rushing_rank < 6 or catching_rank < 6 order by conference, player_name; That's some good stuff! Chris S.
Anon: "So whats the solution for pagination when the query has a join and you are grouping in your CFOUTPUT????" Do a subselect that only queries the primary table; apply the pagination logic on that subquery; and then join your secondary table to the result of the subquery.
Take care of it and keep it on the road! This is a cool site! Thanks and wish you better luck! Brilliant but simple idea. Hi! Guys how you manage to make such perfect sites? Good fellows! Cool design, great info! Hi! Your site appeared very useful to me. Excellent work, thanks.
This is one of the best sites I have ever found. Thanks!!! Very nice and informal. I enjoy being here.
I wrote a little article on pagination: it covers how to do joined queries
Hello, Admin! You are the best!!! Congratulations. Best regards from regular visitor of your site. ;)
Unfortunately offset does not seem to be supported in IBM DB2. Can anyone point to another solution for DB2?
This was a great post. I couldn't use it right away because we use SQL Server 2005 at work. It did, however, get me thinking about getting the database to do pagination. I wrote a search engine last year, and at the time I was wondering if I could get the database to trim out unnecessary rows. Incidentally, I did a quick Google search and found this pagination solution for SQL Server 2005. I'll tinker around with it and see if it works.
How do you "FOUND_ROWS() is greater than offset+limit," inside SQL? Can you do a full Pagination example?
Is there any way to skip the first 5 rows of a query in mysql without limiting the number of results? Offset doesn't seem to work without the keyword 'limit'.
What would be LIMIT clause to select the first 25 records of a table then the next 25?
I want to paginate the records in my oracle database at the client side but its not working.Can you please send me php codes to do exactly what I want. Thanks
I use the SQL in MS Access 2003. Is there any way to paginate in Coldfusion, using the SQL I have?
I want to retrive the result in backward,
how could i use LIMIT AND OFFSET,

Foundeo Inc.