[SOLVED] Select shortest and longest string

Select shortest and longest string

Is it possible to select the shortest and longest strings by characters in a table?

I have a CITY column of type VARCHAR(20) and I want to select the shortest and longest city names in alphabetical order by length.

I did like this

SELECT CITY,LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) IN (
    SELECT MAX(LENGTH(CITY))
    FROM STATION
    UNION
    SELECT MIN(LENGTH(CITY))
    FROM STATION
)
ORDER BY CITY ASC;

When ordered alphabetically, Let the CITY names be listed as ABC, DEF, PQRS, and WXY, with the respective lengths 3,3,4, and 3. The longest-named city is obviously PQRS, but there are options for the shortest-named city; I have to select ABC because it comes first alphabetically.

My query ended up with all three CITY having length 3.

 ABC 3
 DEF 3
 PQRS 4
 WXY 3

The result of SELECT must be

ABC 3
PQRS 4

Solution

Anyway i got the answer

SELECT CITY,LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) IN (
  SELECT MAX(LENGTH(CITY))
  FROM STATION
  UNION
  SELECT MIN(LENGTH(CITY))
  FROM STATION
)
ORDER BY LENGTH(CITY) DESC,CITY ASC LIMIT 2;