'How can I filter column for MAX, MIN and limit each output to 1 each using alphabetically first CITY?
Goal:
- Find MAX AND MIN "length" for CITY column
- Pick 1 result for MAX AND 1 for MIN.
- 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
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 |
