MySQL LIMT is a subquery?-CodePudding

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)

CodePudding user response：

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;

CodePudding user response：

If your version of MySql is 8.0 you can use ROW_NUMBER() window function in a subquery to rank the returned rows based on the order that you want and set the limit in the WHERE clause of the main query instead of the LIMIT clause:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY prod_level ASC, prod_date) rn 
  FROM tbl_prod 
  WHERE prod_id = 32 
) t
WHERE rn <= (SELECT max_count FROM Prod_subscription WHERE prod_id = 32)
ORDER BY rn;