How to make limit offset dynamic using only (My)SQL

Question

This code doesn't work

select pagenr into @offset from pages where id = 3;
select * from table1 limit @offset*10, 10;

What SQLcode do I need to use in order to get this kind of code to work
using only SQL!

Note that

SET SQL_SELECT_LIMIT = @count

doesn't work because I'm mainly concerned with the offset, not the limit as such.

Karelzarath · Accepted Answer · 2011-05-03 18:44:20Z

5

From the MySQL 5.5 specification:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

So, inside a stored procedure, the following would work:

DECLARE offset bigint
SELECT pagenr * 10 INTO offset FROM pages where id = 3;
SELECT * FROM table1 LIMIT offset, 10;

Otherwise, you'll need to precompute the value and pass it in via the query. You should already know the page size and page number, so this shouldn't be difficult.

edited May 3, 2011 at 18:44

answered May 3, 2011 at 18:02

Karelzarath

2971 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bobflux Over a year ago

Aren't @variable session variables ?

Johan Over a year ago

@peufeu, @variables yes, but Kazelzarath is not using @variables.

Karelzarath Over a year ago

I did edit the example to be more complete. Initially, I was just going off what he's already posted.

Collectives™ on Stack Overflow

How to make limit offset dynamic using only (My)SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related