Use select statement as the limit to the outer select statement

Looking to get the top half of a table, I set a select statement that returns an integer as the limit to the outer select statement and got a syntax error.

I checked and ran the select statements separately and they work fine.

My questions are: 1.confirm that the limit cannot be another select statement? 2.If above is true, what the alternative ways to get the top half of the table

select lat_n from station order by lat_n 
limit (select ceil(count(*)/2) from station);

Result:

ERROR 1064 (42000) at line 1: 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 ‘(select ceil(count(*)/2) from station)’ at line 2

Answer

As an alternative:

select t.lat_n 
from (
  select lat_n, @rownum:=@rownum+1 rn 
  from station, (select @rownum:=0) r
  order by lat_n
) t
where t.rn <= (select count(*) from station) / 2

The variable @rownum as a counter pseudocolumn is used so to select only the 1st half rows of the table.

Advertisement

Answer