techmsi techmsi - 4 months ago 49
MySQL Question

MySQL query for shortest & longest length of a field

Problem



I need to return the shortest & longest city names along with their respective lengths from a database. I also welcome refinements to this query to make it more elegant.

SQL Fiddle



I have a sample database & query thus far:
http://sqlfiddle.com/#!9/3b4a7c/1/0

Query



SELECT DISTINCT City, LENGTH(City) as len
FROM STATION
WHERE LENGTH(City)=(SELECT MIN(LENGTH(City)) FROM STATION)
OR LENGTH(City)=(SELECT MAX(LENGTH(City)) FROM STATION)
ORDER BY len


Current Result



Dole 4
Reus 4
Sant'Egidio del Monte Albino 28


Desired Result



Dole 4
Sant'Egidio del Monte Albino 28

Answer

I would do this using union all, order by, and limit . . . if you just want one example of the longest and shortest names:

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

If you wanted ties, when more than one city matches, then I would do:

select s.city, length(s.city)
from station s cross join
     (select max(length(city)) as maxl, min(length(city)) as minl
      from station
     ) ss
where length(s.city) in (ss.minl, ss.maxl);