select min(length(city)), city
from station
group by min(length(city));
select max(length(city)), city
from station
group by min(length(city));
output = ERROR 1056 (42000) at line 1: Can't group on 'min(length(city))'
I am trying to get minimum and maximum char length with city name. I thought this algorithm will be okay.
Why ı can't group on min(length(city))
CodePudding user response:
It doesn't make sense to group by an aggregate function. Aggregate functions are called after grouping, to calculate something for all the rows in the group (such as a sum total).
You can use a subquery to calculate an aggregate for the entire table. Then you can compare this with individual rows.
SELECT city, LENGTH(city) AS length
FROM station
WHERE LENGTH(city) = (SELECT MIN(length(city)) FROM station);
CodePudding user response:
select city, length(city) from station
order by length(city),city asc
limit 1;
select city, length(city) from station
order by length(city) desc
limit 1;