Menu
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.
846
MySQL Offset is used to specify from which row we want the data to retrieve. To be precise, specify which row to start retrieving from. Offset is used along with the LIMIT. Here, LIMIT is nothing but to restrict the number of rows from the output.
In MySQL, the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments that are offset and count. The value of both the parameters can be zero or positive integers.
Limit Data Selections From a MySQL Database Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The SQL query would then look like this: $sql = "SELECT * FROM Orders LIMIT 30"; When the SQL query above is run, it will return the first 30 records.
To get only the specified rows from the table, MySQL uses the LIMIT clause, whereas SQL uses the TOP clause, and Oracle uses the ROWNUM clause with the SELECT statement.
From the MySQL 5.5 specification:
The
LIMITclause can be used to constrain the number of rows returned by theSELECTstatement.LIMITtakes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:
- Within prepared statements,
LIMITparameters can be specified using?placeholder markers.- Within stored programs,
LIMITparameters 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.
125
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
