SQL QUERY - Max and length-CodePudding

My goal is to find the name of the max length of the city. If there is more than one largest city, choose the one that comes first when ordered alphabetically.

I used it below but it's showing an error

select city, max(length(city))
from station
order by city asc
limit 1;

CodePudding user response：

You need to find the city(ies) where the length equals the maximum length, one option would be

select City, length(City)
from station
where Length(city) = (select Max(Length(city)) from station)
order by city
limit 1;

CodePudding user response：

You don't want to select the max, you just want to use it to order:

select city
from station
order by length(city) desc, city asc
limit 1;

CodePudding user response：

Use a sub-select instead:

SELECT city, MAX(len_city)
FROM (SELECT city, LENGTH(city) AS len_city
      FROM station
      ORDER BY LENGTH(city) DESC, city)
LIMIT 1;