Using a set variable inside the LIMIT clause

I'm trying to select the top 50% from a row. The way I'm doing it is by first calculating how many elememts there are in the row, dividing by 2, then using that result inside the LIMIT clause.

SET @UPPER := (SELECT ROUND(COUNT(LONG_W)/2) FROM STATION);
SELECT LONG_W FROM STATION ORDER BY LONG_W DESC LIMIT @UPPER;

I'm doing it in the Hackerrank website, and I do have selected the MySQL dialect.

If I do Select @UPPER; I am getting the expected result, 250 in this case. If I simply write LIMIT 250 it does work, but I'm trying to do it with the variable since I can't always expect to know what is the exact middle.

Currently getting the following error:

> ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@UPPER' at line 1.

I've check to make sure the variable holds the right value and I've manually used that value to check if I get the expected result.

You can't use variables within the LIMIT clause. As per MySQL official documentation:

>LIMIT takes 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.

As suggested by the doc, you can only achieve this using a prepared statement (with a dynamic query) or a stored program.

SET @UPPER := (SELECT ROUND(COUNT(LONG_W)/2) 
               FROM STATION);

SET @sql := CONCAT('SELECT LONG_W 
                    FROM STATION 
                    ORDER BY LONG_W DESC 
                    LIMIT ', @UPPER);
                   
PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Check the demo here.

Dynamic querying is prone to SQL injection. If you feel this may be a relevant problem to your use case, you can execute your dynamic query within a stored procedure, to enforce strong typization of inputs.

SET @UPPER := (SELECT ROUND(COUNT(LONG_W)/2) FROM STATION);

DELIMITER //

CREATE PROCEDURE generate_sample_stations(
  	IN num_stations INT
)
BEGIN
	SET @num_stations = num_stations;

	PREPARE stmt FROM 'SELECT LONG_W 
                       FROM STATION 
                       ORDER BY LONG_W DESC 
                       LIMIT ?';
	
    EXECUTE stmt USING @num_stations;
    
    DEALLOCATE PREPARE stmt;
END//

DELIMITER ;

CALL generate_sample_stations(@UPPER);

Check the demo here.

Note: If you can avoid using variables for your LIMIT clause at all, it's better in the far end.