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.

Related Entries

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


Instead of the OFFSET keyword you can use 2 parameters in LIMIT: SELECT column FROM table LIMIT 10,10
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.
SELECT column FROM table LIMIT 10 OFFSET 10 ORDER BY column Do you know if the LIMIT and OFFSET happen before or after the ORDER clause? For Oracle: SELECT column FROM table WHERE ROWNUM BETWEEN 10 AND 20 ORDER BY column I believe that the ORDER clause is applied only to the records that fit the WHERE clause. Not very useful for pagination where ordering is usually required.
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.
Yes, ROWNUM on Oracle is quite nasty compared to LIMIT and OFFSET. It applies the order by *after* the ROWNUM part of the WHERE clause. So, Oracle will grab rows 10-20 of a randomly ordered set and only *then* order them. If you want to truly get pagination in oracle you need to use your query as a subselect: SELECT * FROM (SELECT column FROM table ORDER BY column) WHERE ROWNUM BETWEEN 10 AND 20
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 :(
Hola Un ejemplo para SQL SERVER para emular LIMIT SQL=" ,(SELECT TOP 1 '<table width=100%><tr><td>'+ISNULL(CIU.nombre,')+'</td><td>'+ISNULL(CIU.apellido,') + '</td><td>'+ ISNULL(CIU.edad,') + '</td><td>' + ISNULL(CASE CIU.sexo WHEN 1 THEN 'Masculino' WHEN 2 THEN 'Femenino' END,')+'</td><td>'+ ISNULL(SUBSTRING(REG.nombre,1,3)+'-'+DEP.nombre+'-'+MUN.nombre,'NACIONAL')+'</td></tr></table>' AS NOMBRE " + " FROM departamento DEP INNER JOIN" + " municipio MUN ON DEP.id_departamento = MUN.id_departamento INNER JOIN" + " regional REG ON DEP.id_regional = REG.id_regional RIGHT OUTER JOIN" + " ciudadano CIU INNER JOIN" + " respuesta RES ON CIU.id_ciudadano = RES.id_ciudadano ON MUN.id_municipio = CIU.id_municipio" + " WHERE (RES.id_pregunta = " + id_pregunta + ") AND (RES.id_cuestionario =CUES.id_cuestionario ) " + " AND CIU.id_ciudadano NOT IN (SELECT TOP 3 CIU.id_ciudadano " + " FROM ciudadano CIU INNER JOIN" + " respuesta RES ON CIU.id_ciudadano = RES.id_ciudadano" + " WHERE RES.id_pregunta = " + id_pregunta + " AND RES.id_cuestionario =CUES.id_cuestionario )) AS CONTACTO_4_"+id_pregunta;
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)
MS SQL Server: SELECT * FROM ( SELECT TOP $numRows *, $order AS Ordenar FROM ( SELECT TOP $lastRow * FROM $table WHERE $condition ORDER BY $order ASC) x ORDER BY x.$order DESC) t ORDER BY t.Ordenar
Firebird has a different syntax SELECT FIRST 10 SKIP 10 * FROM ... selects 10 rows after skipping 10 from table
>>>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
but that way will NOT WORK as spected so you will have to use the 1 way and hide the column rn. tanks.
Ok, but what if you want to select * from Table and you do not known column names so you cannot SELECT column1 c, columnt2 c2, rownum r
when you use rownum to paginate a query in oracle, you must know that rownum is order by rowid. Because of this, if you need to order by other colum not rowid (a date for example), rownum appears unshorted
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.
PAGINATION WITH ORACLE WHERE YOU NEED ORDER BY ANY VARCHAR: ====================================== QUERY: SELECT * FROM( SELECT a.*, ROWNUM r FROM ( SELECT column FROM table[,....] [WHERE clause] ORDER BY column[,...]) a ) WHERE r BETWEEN start AND end
There's some real shit in this thread about Oracle and ROWNUM. ROWNUM does not have much to do with ROWID (as some state). If you only want to get ROWNUM in a query and do not want to limit the results on the ROWNUM, then you do not need any subqueries (the alias "r" for ROWNUM is not required in this case): SELECT a.*, ROWNUM r FROM some_table a ORDER BY a.some_column When you want to limit on ROWNUM, then you need only one subquery for it (giving an alias to ROWNUM is crucial here) including GROUP BY cases: SELECT * FROM ( SELECT a.*, ROWNUM r FROM some_table a ORDER BY a.some_column ) WHERE r BETWEEN 2 AND 22 ORDER BY r I'm not talking about a good way to paginate, but only about using ROWNUM to limit results. It may be not a good thing to use in pagination.
Dang. I do not know how to force line break on this blog, so my previous comment is pretty unreadable. I wonder if HTML tags work.<br> I'll check it and repost my previous comment.
Maybe this will work: a[br] b[lb] c[break] d&#13;
well Hilarion, referencing to your answer after my solution to the pagination with order by when we need to do order by with any varchar field. ----------------------------------- i have putted your solution but it does not seems towork with the varchar try it with the scott scehma with emp table you will also convinced on my solution your solution was: and i have putted it into scott schema SELECT * FROM ( SELECT a.*, ROWNUM r FROM EMP a ORDER BY a.ENAME ) WHERE r BETWEEN 2 AND 4 ORDER BY r it gives me result like follwoing ================================= EMPNO ENAME JOB R 7499 ALLEN SALESMAN 2 7521 WARD SALESMAN 3 7566 JONES MANAGER 4 =================================== but i want result like below EMPNO ENAME JOB R 7499 ALLEN SALESMAN 2 7698 BLAKE MANAGER 6 7782 CLARK MANAGER 7 ================================= i got this only from my below query SELECT * FROM ( SELECT b.*, ROWNUM r1 FROM ( SELECT a.empno, a.ename, a.job, ROWNUM r FROM EMP a ORDER BY a.ENAME ) b ) WHERE r1 BETWEEN 2 AND 4 ORDER BY r ================================== may be there are any othe better solution then this but i haven't find your solution workable according to the requirements.
I'm as dumb, as it gets and Seemal Mushtaq is totally right. Actually one CAN limit returned rows using ROWNUM with only one subquery, but not with BETWEEN operator. The only thing that can be done with only one subquery is returning first N rows, and it goes like this: SELECT a.* FROM ( SELECT something FROM somewhere ORDER BY something ) a WHERE ROWNUM <= 5 ORDER BY ROWNUM. The only operators that can be used in this case are "<" and "<=". Any other operator will cause the query to return no results (at least in Oracle 9i). One can check this link for a bit more efficient (but a bit less readable) way to get some middle rows: <>. PS.: Can anyone tell me how to make a line-break in comments in this blog? I can't find the right way. I also noticed something: comments with line-breaks tend to move the comment texts a bit to the left - it looks like some kind of blog mechanism error (it renders HTML improperly).
can any body help me to find out the total actual experience of employeess without calculating overlap experience. i have used the following query ============================== SELECT TO_NUMBER(SUBSTR(A,1,4)) - 2000 years, TO_NUMBER(SUBSTR(A,6,2)) - 01 months, TO_NUMBER(SUBSTR(A,9,2)) - 01 days, SUBSTR(A,12,2) hours, SUBSTR(A,15,2) minutes, SUBSTR(A,18,2) seconds FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD') + ( SELECT SUM(TRUNC((((86400*(JOB_HISTORY.END_DATE - JOB_HISTORY.FROM_DATE))/60)/60)/24) ) FROM JOB_HISTORY WHERE employee_id='0564' ),'YYYY MM DD HH24:MI:SS') A FROM DUAL) but it also add up the experience of also those jobs which have been done simultaneously. for example for this particular employee with following job history ================= DAYS END_DATE FROM_DATE 819 30-Apr-06 01-Feb-04 668 30-Apr-06 01-Jul-04 experience should be shown near about 2 years 2 months AND 29 days but my query shows it like below YEARS MONTHS DAYS 4 0 26 which is incorrect but for above the following query gives accurate result SELECT TRUNC( MONTHS_BETWEEN( MAX(END_DATE),MIN(FROM_DATE) ) /12 ) Years, MOD( TRUNC( MONTHS_BETWEEN( MAX(END_DATE),MIN(FROM_DATE) ) ), 12 ) months, MAX(END_DATE) - ADD_MONTHS(MIN(FROM_DATE),TRUNC( MONTHS_BETWEEN( MAX(END_DATE),MIN(FROM_DATE) ) )) days FROM JOB_HISTORY WHERE employee_id='1380' ORDER BY end_date DESC but it fails when an employee having experience with some pauses ============== DAYS END_DATE FROM_DATE 731 01-Feb-93 01-Feb-91 586 09-Dec-99 02-May-98 for this particular case my first query runs smoothly. i will really appreciate if any body answer my this question as soon as possible withing one query. Thanx in advance
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.
Hello, Admin! You are the best!!! Congratulations. Best regards from regular visitor of your site. ;)
Hello, Admin! You are the best!!! Congratulations. Best regards from regular visitor of your site. ;)
Hello, Admin! You are the best!!! Congratulations. Best regards from regular visitor of your site. ;)
Hello, cool site - nice navigation keep going do it! You will have them for our next
I wrote a little article on pagination: it covers how to do joined queries
how to get previous 30 dates in a single coloumn like 11/11/2006 11/10/2006 11/09/2006 so on
Hello, Admin! You are the best!!! Congratulations. Best regards from regular visitor of your site. ;)
you can use select top 1% * from table where .. order by ....
one example for MS SQL 2005:
See You can use select row_number() over(order by id) as rn,...
Unfortunately offset does not seem to be supported in IBM DB2. Can anyone point to another solution for DB2?
i tried the rownum for oracle, but when i performed it, it somehow made my table expand to 4 rows instead of 2 (there are technically 2 rows with 4 users associated with them, but i have a function that concatenates the usernames together, which seems to be working) any ideas as to why it would do that?
can you please post the query so that problem would be clear.
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'.
Thanks for saving my bacon.
In php why does the following query do not return correct results for some higher numbers? "SELECT * FROM products WHERE products_status=0 ORDER BY products_id LIMIT $rows_per_page OFFSET $offset" i have set $rows_per_page=9; but still in some pages the results are greater than 9.can anybody explain this to me?? and what is the possible solution for that?
appreciation you unequivocally much for the news provided on the milieu. will turn any questions to ask admin soap.
What would be LIMIT clause to select the first 25 records of a table then the next 25?
Oracle's Tom Kyte on pagination with ROWNUM, this should help:
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
For PostgreSql you need to supply an ORDER BY in your LIMIT OFFSET based query.

Otherwise you get unpredictable results!
I tested this behaviour with a Pg 8.2 and a table with 3M rows.
After 240.000 rows I got a row chunk which already appeared after 20.000 rows.

So, you need an order by!
Hi Pete,
I have a requirement to get total no of records and top 500 records from 2 tables joining as inner join

I found something like this:

SELECT COUNT(*) FROM `stations` WHERE StationName LIKE 'a%' LIMIT 500;


and it is advised to have second query just after first to read the no of records, now my problem is, how can I get result for first query and total no of records from a single cfquery?

please let me know if I am not clear about my requirement?

I am sorry I put the wrong first query, here is the right one

SELECT SQL_CALC_FOUND_ROWS `StationName`, `CRScode` FROM `stations` WHERE StationName LIKE 'a%' LIMIT 500;

I have the same opinion with most of your points, however some need to be discussed further, I will hold a small conversation with my buddies and perhaps I will ask you some suggestion shortly.

- Henry
I use the SQL in MS Access 2003. Is there any way to paginate in Coldfusion, using the SQL I have?
prodTable.DataSource = Peulot.getDataView(commandString);
I want to retrive the result in backward,
how could i use LIMIT AND OFFSET,
Sir,LIMIT is not supported in SQL. Try to run this in mySQL. Although it is supported in SQL server.
Great posting, thanks Pete and ALL!

Recent Entries


did you hack my cf?