CODEWITHSUNDEEP
mysqlsqllimit
Johan
Johan

Reputation: 76537

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

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.

Upvotes: 7

Views: 15240

Answers (1)

Karelzarath
Karelzarath

Reputation: 297

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.

Upvotes: 5

Related Questions