10

I have an error in my syntax :

SET @start := 0;
SELECT (ROUND((count(item))/2)) FROM car INTO @until; 

SELECT * FROM car
LIMIT @until OFFSET @start;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@until OFFSET @start' at line 1

Anyone can help me?
Thanks

informatik01
  • 16,038
  • 10
  • 74
  • 104
empugandring
  • 561
  • 1
  • 5
  • 15

2 Answers2

5

You cannot use a user-defined variable (@until) in the LIMIT clause.

A possible solution (a variation on this):

SELECT (ROUND((count(item))/2)) FROM car INTO @until;
SELECT * FROM (
  SELECT *, 
         @rownum := @rownum + 1 AS rank
    FROM car, 
         (SELECT @rownum := 0) r
) d WHERE rank < @until;

Only downside is you lose the offset, but you can accommodate that by adjusting the WHERE clause. Otherwise, you can use a stored procedure.

informatik01
  • 16,038
  • 10
  • 74
  • 104
kasimir
  • 1,506
  • 1
  • 20
  • 26
  • [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= @until' at line 1 – empugandring Sep 25 '14 at 08:44
  • Scrap that, the problem is with your usage of the variable with `LIMIT`. It appears you can't use it there: http://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql. I'll update my answer with a possible solution. – kasimir Sep 25 '14 at 09:56
1

You could try something like this:

DELIMITER //
CREATE PROCEDURE pagination(
    INT pageno INT
)
BEGIN
    DECLARE pages INT;
    DECLARE pagefrom INT;

    SET pages = 1000; -- get this many records at a time
    SET pagefrom = pageno * pages; -- 2 * 1000 = 2000

    SELECT *
    FROM car
    LIMIT pagefrom, pages;
END //
DELIMITER ;

CALL pagination(2);

This example will get records from 2000 to 3000

kurdtpage
  • 3,142
  • 1
  • 24
  • 24
  • For those who didn't immediately see the point: instead of using a [_user-defined_](https://dev.mysql.com/doc/refman/8.0/en/user-variables.html) variables, you can use the `DECLARE` syntax for defining [_local variables_](https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html). Local variables declared in such manner can be used with `LIMIT`. Just remember that `DECLARE` statements must be written first inside the body of a prepared statement. – informatik01 Jul 21 '21 at 01:30