SQL] Hackerrank quiz

분명 Easy 단계인데 오랜만에 sql 써보려니 헷갈리던 문제

hackerrank - Weather Observation Station 5

Problem

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.

Sample Input

Let’s say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output

ABC 3
PQRS 4

• 러프한 답안

SELECT
    city,
    CHAR_LENGTH(city)
FROM STATION
WHERE city = (
    SELECT
        MIN(city)
    FROM STATION
    WHERE CHAR_LENGTH(city) = (
        SELECT
            MIN(CHAR_LENGTH(city))
        FROM STATION
    )
)
OR city = (
    SELECT
        MIN(city)
    FROM STATION
    WHERE CHAR_LENGTH(city) = (
        SELECT
            MAX(CHAR_LENGTH(city))
        FROM STATION
    )
);

유저 지정 변수를 이용

• 데이터가 많지 않을 경우에는 오히려 더 오래 걸린다.

# find shortest city name
SET @MinCityLen = (SELECT MIN(CHAR_LENGTH(city)) FROM STATION);
# find longest city name
SET @MaxCityLen = (SELECT MAX(CHAR_LENGTH(city)) FROM STATION);

SELECT
    city,
    CHAR_LENGTH(city)
FROM
    STATION
WHERE
    # find shortest city name sorted alphabetically
    city = (
        SELECT
            city
        FROM STATION
        WHERE CHAR_LENGTH(city) = @MinCityLen
        ORDER BY city ASC
        LIMIT 1
    )
    # find longest city name sorted alphabetically
    OR city = (
        SELECT
            MIN(city)
        FROM STATION
        WHERE CHAR_LENGTH(city) = @MaxCityLen
        ORDER BY city ASC
        LIMIT 1
    );

쿼리를 두 번 날리는 답안

• 굳이 복잡하게 할 필요 없는 것 같다.
• 아마 출제자도 이 답안을 생각하고 내지 않았을까.

Note
You can write two separate queries to get the desired output. It need not be a single query.

SELECT name, LENGTH(name) FROM CITY ORDER BY LENGTH(name), name ASC LIMIT 1;
SELECT name, LENGTH(name) FROM CITY ORDER BY LENGTH(name) DESC LIMIT 1;