Select shortest and longest string

Question

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

Answer 1

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;

Answer 2

I know you have chosen your answer already, but here is a shorter answer that might help. This is using Microsoft MySQL Server but it can also be easily translated to any other type using the call LIMIT instead of TOP.

Shortest Length

SELECT TOP 1 CITY, LEN(CITY)
FROM STATION 
ORDER BY LEN(CITY) ASC, CITY ASC;

Longest Length

SELECT TOP 1 CITY, LEN(CITY)
FROM STATION 
ORDER BY LEN(CITY) DESC, CITY ASC;