SQL query - limit based on subquery

Question

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

eggyal · Accepted Answer · 2013-06-12 20:42:33Z

1

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:

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;

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);

answered Jun 12, 2013 at 20:42

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user1893354 Over a year ago

OK now i need a way do do this without declaring a variable. Is this possible?

eggyal Over a year ago

@user1893354: How do you mean? Using the second method above, one could CALL foo(123);.

user1893354 Over a year ago

but you would still need to declare the @v1 variable, wouldn't you?

user1893354 Over a year ago

in the last line 'CALL foo(@v1)'. I assume @v1 would need to be declared in order to do this

eggyal Over a year ago

@user1893354: See my previous comment - "Using the second method above, one could CALL foo(123);".

|

Collectives™ on Stack Overflow

SQL query - limit based on subquery

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related