CODEWITHSUNDEEP
mysqlvariableslimit
empugandring
empugandring

Reputation: 571

MySQL limit with variable

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

Upvotes: 10

Views: 10284

Answers (2)

kasimir
kasimir

Reputation: 1554

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.

Upvotes: 5

kurdtpage
kurdtpage

Reputation: 3221

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

Upvotes: 1

Related Questions