SELECT * FROM user LIMIT (SELECT group_limit FROM groups WHERE groupid = 7471);
Add a comment
|
3 Answers
This is from the MySQL Database Knowledge base:
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
For your query to work, you will need to write it as a prepared statement, and then execute that.
SET @a = (SELECT group_limit FROM groups WHERE groupid = 7471);
PREPARE STMT FROM 'SELECT * FROM user LIMIT ?';
EXECUTE STMT USING @a;2 Comments
user742102
this does not work on mysql query browser, returned no result and when I put it in BIRT query, it returns error. Guess it is not recognized. coz when I type it in toad, it works. How can I make this work in mysql?
Steven Ryssaert
What MySQL Database version are you using?
Since MySQL 8.0.2, the ROW_NUMBER() window function has been supported. With the help of a subquery, this means you can do something close to what the question is asking for:
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY 1) rownum, u.* FROM user u
) t
WHERE t.rownum <= (SELECT group_limit FROM groups WHERE groupid = 7471);
Comments
If you are using MariaDB, you can use sequence tables to achieve this.
SELECT
u.*
FROM
user u
INNER JOIN
seq_0_to_999999 lim
ON
lim.seq < (SELECT group_limit FROM groups WHERE groupid = 7471)
Depending on your DB structure, you might even simplify this further by joining the groups table instead of using a subquery, but I haven't tested which is faster.
Unfortunatly, I couldn't find the official MariaDB documentation on this, but I use sequence tables in production code. Also it is used in this SO answer: https://dba.stackexchange.com/a/264109/252546
