Search code examples
mysqlselectsubquerylimit

MySQL LIMT is a subquery?

I want to use a select statement to control the limit of another select query, I cant get it to work, what I have below. The Select in the braces returns an INT. Im newer to MySQL so im not sure what I should use instead to get this to work.

SELECT * 
FROM `tbl_prod` 
WHERE prod_id = 32 
ORDER BY prod_level ASC , prod_date 
LIMIT 
    (SELECT max_count 
    FROM Prod_subscription 
    WHERE prod_id = 32)
Solution

  • You can't write subquery in LIMIT, but you can use dynamic SQL to make your expected result.

    SET @num = (
    SELECT max_count 
    FROM Prod_subscription 
    WHERE prod_id = 32);

PREPARE STMT FROM 'SELECT * 
FROM `tbl_prod` 
WHERE prod_id = 32 
ORDER BY prod_level ASC , prod_date 
LIMIT ?';
EXECUTE STMT USING @num;