how to set the value of LIMIT using select count(id) from another table |MySQL

Question

I have a scenario where the result must be limited depends on counting ids in another table.

Suppose i have these two tables counter and dispanser,

i want to select the last records in the table counter and limit the selection by counting the number of records in dispanser table.

something like this

select * from counter limit (select count(dispID) from dispanser) 

Answer 1

Update

As of MySQL 8+, you can use ROW_NUMBER() to count rows in counter, and select only rows whose row number is less than the number of values in dispanser:

WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM counter
)
SELECT id, name
FROM cte
WHERE rn <= (SELECT COUNT(dispID) FROM dispanser)

Output for your sample data (same as original answer):

id  name
1   sorabh
2   john
3   sorabh
4   john
5   sorabh

Demo on dbfiddle.uk

Original Answer (MySQL 5.7 and below)

You can't do this without using prepared statements or a stored procedure. From the manual:

LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants

In a stored procedure you could do something like this. COUNT(dispID) is stored into cnt and that variable is then used as the parameter to LIMIT. This is the exception to the above-mentioned rule.

DELIMITER //
CREATE PROCEDURE select_counter()
BEGIN
  DECLARE cnt INT;
  SELECT COUNT(dispID) INTO cnt FROM dispanser;
  SELECT * FROM counter LIMIT cnt;
END //
DELIMITER ;

DBFiddle