Pete Freitag Pete Freitag

SQL Pagination with LIMIT and OFFSET

Updated on January 31, 2024
By Pete Freitag

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

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

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

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

sql limit offset mysql postgresql tips

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:

Discuss / Follow me on Twitter ↯


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?
by Barney on 08/29/2005 at 4:40:24 PM UTC
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.
by Pete Freitag on 08/29/2005 at 4:42:42 PM UTC
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

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.
by Pete Freitag on 08/29/2005 at 4:48:13 PM UTC
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.
by Barney on 08/30/2005 at 12:49:53 AM UTC
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.
by Barney on 08/30/2005 at 12:52:53 AM UTC
Thanks for pointing out the FOUND_ROWS() function Barney, I had read about it but didn't test it.
by Pete Freitag on 08/30/2005 at 8:32:48 AM UTC
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,

by Bob Afifi on 08/31/2005 at 11:16:54 AM UTC
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.

by Pete Freitag on 08/31/2005 at 11:28:40 AM UTC
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,

by Bob Afifi on 08/31/2005 at 3:17:15 PM UTC
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.
by Pete Freitag on 08/31/2005 at 3:45:28 PM UTC
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:


You get 0. Don't use it.
by Matt on 09/14/2005 at 3:21:06 PM UTC
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.
by Barney on 09/14/2005 at 4:13:10 PM UTC
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) :


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).
by Jean on 09/27/2005 at 10:55:53 AM UTC
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 :(
by jorgempf on 11/07/2005 at 11:17:07 AM UTC
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)
by will on 12/27/2005 at 10:34:38 PM UTC
>>>select first 20 records<<<

select * from (SELECT fields,rownum r FROM table ) where r between 0 and 20
by Fariborz on 02/13/2006 at 4:30:51 AM UTC
hi all....
I have an solution for the pagination in oracle.
its using
with the use of
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
"yourrowid" < city.rowid
rownum < 100;

this will fech 100 records starting from yourrowid.

Rowid is an unique value for each row in oracle.
by Neo on 02/20/2006 at 7:53:20 PM UTC
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=...
Where rn between rowI and rowJ
by Juan Antonio Sanjuan Cuellar on 03/08/2006 at 7:20:12 AM UTC
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
by Juan Antonio Sanjuan Cuellar on 03/08/2006 at 7:25:38 AM UTC
To do OFFSET and LIMIT with SQL Server, see here:

Instructions for

- SQL Query
- Stored Procedure
- User-Specific Records
- Application-Wide Records
by Tidy Technologies on 04/25/2006 at 3:30:01 PM UTC
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,
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,
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,
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,
from player_stats)
where passing_rank < 6
or rushing_rank < 6
or catching_rank < 6
order by conference,

That's some good stuff!
Chris S.
by csmith3pmg on 05/05/2006 at 12:48:24 PM UTC
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.
by Matt Westcott on 08/18/2006 at 9:07:24 AM UTC
Unfortunately offset does not seem to be supported in IBM DB2. Can anyone point to another solution for DB2?
by mike on 11/12/2007 at 8:14:41 PM UTC
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.
by Jose Galdamez on 04/02/2008 at 3:24:52 PM UTC
How do you "FOUND_ROWS() is greater than offset+limit," inside SQL? Can you do a full Pagination example?
by dan on 04/23/2008 at 11:59:51 PM UTC
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'.
by Marc on 06/25/2008 at 3:37:50 PM UTC