Home > Blockchain >  SQL Order By min(len()
SQL Order By min(len()

Time:04-14

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;
  • Related