my question is the following one I have a query for example:
SELECT * FROM PERSONA LIMIT 0,5;
So far so good, but I want instead of 5 to receive a consult count(*) , for example:
SELECT * FROM PERSONA LIMIT 0,(SELECT COUNT(*) FROM TABLA1);
The detail is that when I make the query it generates syntax error.
I appreciate the help!
According to the reference manual :
The
LIMITclause can be used to constrain the number of rows returned by the SELECT statement.LIMITtakes one or two numeric arguments , which must both be nonnegative integer constants, with these exceptions:
Within prepared statements, LIMIT parameters can be specified using? placeholder markers.
Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.
Quickly translated: it only supports numerical values directly, except for two exceptions: in prepared queries, you can use a dynamic marker. Or use a stored procedure, where it would admit, a local variable.
Let's apply both solutions:
SET @limitTwo=(SELECT COUNT(*) FROM TABLA1);
PREPARE STMT FROM 'SELECT * FROM PERSONA LIMIT O,?';
EXECUTE STMT USING @limitTwo;
CREATE PROCEDURE customLimit()
BEGIN
DECLARE limitTwo INT DEFAULT 1;
SET limitTwo = (SELECT COUNT(*) FROM TABLA1) ;
SELECT * FROM PERSONA LIMIT 0,limitTwo;
END;
We call it to show the data:
CALL customLimit();
try this.
DECLARE @Total int;
SELECT @Total = (SELECT COUNT(*) FROM TABLA1);
SELECT * FROM PERSONA LIMIT 0,@Total;