CODEWITHSUNDEEP
mysqllimit
user1893354
user1893354

Reputation: 5938

SQL query - limit based on subquery

I'm trying to get the top half of entries whose flag field equals 1. I tried using a variable to hold the limit value as in

set @v1:=(select ceil(count(*)/2) as top_half from my_table
where flagged=1);

select * from my_table where flagged=1 order by 21_day_probability limit @v1;

But this does not work. Any suggestions?

Thanks

Upvotes: 0

Views: 154

Answers (1)

eggyal
eggyal

Reputation: 125865

As documented under SELECT Syntax:

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, 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.

Therefore LIMIT parameters can never be user-defined variables. Your options, as indicated above, are to use either:

  1. prepared statements

    PREPARE stmt FROM '
  select * from my_table where flagged=1 order by 21_day_probability limit ?
';
EXECUTE stmt USING @v1;
DEALLOCATE PREPARE stmt;

  2. a parameter/local variable within a stored program:

    CREATE PROCEDURE foo(_l INT)
  select * from my_table where flagged=1 order by 21_day_probability limit _l
;
CALL foo(@v1);

Upvotes: 1

Related Questions