'How can I filter column for MAX, MIN and limit each output to 1 each using alphabetically first CITY?

Goal:

  1. Find MAX AND MIN "length" for CITY column
  2. Pick 1 result for MAX AND 1 for MIN.
  3. If we have more than 1 MAX or MIN output, then pick 1 CITY with highest Alphabetical order (ie. Closest to "A")

Issue: I can't seem to filter and narrow down my results alphabetically to a single CITY with MAX and MIN LENGTH(CITY). Could someone advise on how best could I perform this action effectively without hardcoding the "CITY='Amo'" in the query below?

Table Name: STATION - Strcuture

Incomplete Solution #1:

SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE (
    LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
    OR LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
    )
ORDER BY CITY ASC
;

OUTPUT:

Amo 3 
Lee 3 
Marine On Saint Croix 21 
Roy 3 

Incomplete Solution #2:


SELECT CITY ASC, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
UNION ALL
SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
;

OUTPUT:

Roy 3 
Amo 3 
Lee 3 
Marine On Saint Croix 21 

Incompetent Solution:

SELECT CITY ASC, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
UNION ALL
SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
AND CITY = 'Amo' 
;

CORRECT OUTPUT:

Amo 3 
Marine On Saint Croix 21 
sql


Solution 1:[1]

You don't specify any RDBMS however using analytic functions (supported by most popular RDBMS) you could do something like:

with c as (
    select *,
      Row_Number() over(order by Length(city),city) MinCity,
      Row_Number() over(order by Length(city) desc,city) MaxCity
    from Station
)
select City, Len(city) 
from c
where MinCity = 1 or MaxCity = 1
order by City

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu