We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
-- create city, number of length
WITH city_lengths AS (
SELECT CITY, CHAR_LENGTH(CITY) AS len
FROM STATION
),
-- set first condition with the longest_city
longest_city AS (
SELECT CITY, len
FROM city_lengths
WHERE len = (SELECT MAX(len) FROM city_lengths)
ORDER BY CITY ASC
LIMIT 1
),
-- set second condition with the shortest_city
shortest_city AS (
SELECT CITY, len
FROM city_lengths
WHERE len = (SELECT MIN(len) FROM city_lengths)
ORDER BY CITY ASC
LIMIT 1
)
-- combine them together
SELECT * FROM longest_city
UNION
SELECT * FROM shortest_city;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
-- create city, number of length WITH city_lengths AS ( SELECT CITY, CHAR_LENGTH(CITY) AS len FROM STATION ), -- set first condition with the longest_city longest_city AS ( SELECT CITY, len FROM city_lengths WHERE len = (SELECT MAX(len) FROM city_lengths) ORDER BY CITY ASC LIMIT 1 ), -- set second condition with the shortest_city shortest_city AS ( SELECT CITY, len FROM city_lengths WHERE len = (SELECT MIN(len) FROM city_lengths) ORDER BY CITY ASC LIMIT 1 ) -- combine them together SELECT * FROM longest_city UNION SELECT * FROM shortest_city;