HackerRank SQL - Weather Observation Station 5

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
The STATION table is described as follows:

Problem

STATION의 테이블에서 길이가 가장 짧은 CITY 명과 가장 긴 CITY 명을 출력

단, 동일한 길이의 CITY 명은 알파벳순으로 정렬하여 첫 번째 오는 CTIY 명이 출력되도록

---

Answer1

(SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MIN(CHAR_LENGTH(CITY))
                             FROM STATION)
ORDER BY CITY ASC
LIMIT 1)

UNION ALL

(SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MAX(CHAR_LENGTH(CITY))
                             FROM STATION)
ORDER BY CITY ASC
LIMIT 1)

Answer2

생각해보니 WHERE절도 필요없겠다. 그냥 정렬을 하면 되구나.

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
ORDER BY CHAR_LENGTH(CITY) ASC, CITY ASC
LIMIT 1;

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
ORDER BY CHAR_LENGTH(CITY) DESC, CITY ASC
LIMIT 1

 

 

---

How to solve

WHERE절 서브쿼리를 써서 MIN, MAX를 잡아보기로 하고 접근했다.

약간 찝찝한 부분은 중복이 너무 많아보인다는 점.

 

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MIN(CHAR_LENGTH(CITY)) 
                             FROM STATION)
ORDER BY CITY ASC

 

 

그런데 이러면 MIN(CHAR_LENGTH(CITY)) 값이 unique하지 않을 경우,

하나 이상의 값이 출력된다. 그럼 두번째 조건에 위배되니, LIMIT를 넣어준다.

 

 

LIMIT  1 넣고 UNION ALL 적으니 오류뜬다.

 

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MIN(CHAR_LENGTH(CITY)) 
                             FROM STATION)
ORDER BY CITY ASC
LIMIT 1

UNION ALL

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MAX(CHAR_LENGTH(CITY)) 
                             FROM STATION)
ORDER BY CITY ASC
LIMIT 1

 

각 쿼리를 괄호로 묶어주면 된다.

나중에 보니 그냥 UNION ALL 대신 ';'로 묶어주면 된다더라.

 

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MIN(CHAR_LENGTH(CITY)) 
                             FROM STATION)
ORDER BY CITY ASC
LIMIT 1;

SELECT CITY, CHAR_LENGTH(CITY)
FROM STATION
WHERE CHAR_LENGTH(CITY) = (SELECT MAX(CHAR_LENGTH(CITY)) 
                             FROM STATION)
ORDER BY CITY ASC
LIMIT 1