Get LIMIT value from subquery result

user_14708611 0
Viewed 50

I would like to use the LIMIT option in my query, but the number of expected rows is stored in another table. This is what I have, but it doesn't work:

select * from table1 limit (select limitvalue from table2 where id = 1)

When I only run the subquery, the result is 6, as expected.

I prefer working with a WITH statement if possible, but that didn't work eiter. Thank you in advance!

mariadb subquery with-statement
2 Answers

You could use a prepared statement to get the limit of queries from the other table because the limit clause does not allow non constant variables as parameter:

PREPARE firstQuery FROM "SELECT * FROM table1 LIMIT ?";
SET @limit = (select limitvalue from table2 where id = 1);
EXECUTE firstQuery USING @limit;

The source of the sql query from another post

user_181844950

You can make use of MariaDB's ROW_NUMBER function in a CTE to count the rows to be output, comparing that against the limitvalue. For example:

WITH rownums AS (
  SELECT *,
         ROW_NUMBER() OVER () AS rn
  FROM table1
)
SELECT *
FROM rownums
WHERE rn <= (SELECT limitvalue FROM table2 WHERE id = 1)

Note Using LIMIT without ORDER BY is not guaranteed to give you the same results every time. You should include an ORDER BY clause in the OVER part of the ROW_NUMBER window function. With the sample data in my demo, you might use something like:

ROW_NUMBER() OVER (ORDER BY mark DESC)

Demo on dbfiddle

user_94737640
Related