MySql Using multiple queries simultaneously

Question

I am fairly new to sql and am trying to understand the working of Limit. I wrote this code in MySql using the following code inspired by https://stackoverflow.com/a/1309211/11570812.

select count(*) from station as c;
select * from station
order by ID
limit c;

But it throws the following error

ERROR 1327 (42000) at line 2: Undeclared variable: c

Can anyone suggest how to use the value from one query into another query and the reason for this error.

What are you trying to do? The limit won't really help since you want to get all the rows anyway... is it? — The Impaler, Aug 20 '20 at 15:52
I know this code won't do anything different but to learn how to use values found from one query into another is my primary objective. — Aman Oswal, Aug 20 '20 at 16:03

score 1 · Answer 1 · answered Aug 20 '20 at 16:19

1

I would recommend percent_rank():

select *
from (
    select s.*, percent_rank() over(order by id) pr
    from station s
) s
where pr <= 0.2

answered Aug 20 '20 at 16:19

GMB

216,147
25
84
135

1

Here's a doc link to the [percent_rank() function](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_percent-rank). Note this requires MySQL 8.0. – Bill Karwin Aug 20 '20 at 16:23

score 0 · Answer 2 · answered Aug 20 '20 at 16:06

To produce the result you want you'll need to combine those two queries into a single one. You do that by using subqueries.

For example, if you wanted the first half of the table (ordered by ID) you could use two subqueries:

select
from ( -- this is a "table expression" subquery
  select *,
    row_number() over(order by id) as rn
  from station
) x
where rn <= ( -- this is a typical "old school" subquery
  select count(*) / 2 from station
);

score 0 · Answer 3 · answered Aug 20 '20 at 16:27

There are two problems:

select count(*) from station as c;

select * from station
order by ID
limit c;

Problem 1: These are two separate queries. Column aliases only last until the end of the query they are defined in. Therefore you can't reference c in the second query.

Problem 2: LIMIT requires either an integer literal or a query parameter. You can't use aliases or variables as the arguments for LIMIT.

So you can't do what you show, without using dynamic SQL. I showed this in the answer you linked to.

MySql Using multiple queries simultaneously

3 Answers3