pf » LIMIT and OFFSET SQL Pagination

SELECT column FROM table
LIMIT 10

SELECT column FROM table
LIMIT 10 OFFSET 10

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?

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.

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

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

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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp

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: <http://www.techonthenet.com/oracle/questions/middle_records.php>. 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:

http://www.score5.org/articles/show/3

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: http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

See http://oraqa.com/2006/02/09/how-to-paginate-through-an-ordered-result-set/ 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?

HEY EVERYBODY!!!

so..can anybody tell me...whats the finally solution for pagination with ORACLE... I HAVE A PROGRAM IN JAVA AND I CONSULT WITH A QUERY, SOME REGISTER FROM ORACLE BD. THEN I PRINT THIS INFORMATION IN A JTABLE AND WELL I HAVE A PROBLEM HERE BECAUSE IT'S TO MUCH INFORMATION AROUND 2.500.000 REGISTERS.. SO I NEED HELP WITH THE PAGINATION THING.

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.

http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

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?