[SQL문제풀기] Weather Observation Station 5

문제

Weather Observation Station 5 | HackerRank

Write a query to print the shortest and longest length city name along with the length of the city names.

https://www.hackerrank.com/challenges/weather-observation-station-5/problem?isFullScreen=true

MySQL

내가 작성한 오답

select city, min(length(city)) len from station order by 1
union all
select city, max(length(city)) len from station order by 1

내가 작성한 정답1 - row_number()

select city, len
from (select city, length(city) len 
            , row_number() over(order by length(city) desc, city asc) max
            , row_number() over(order by length(city), city) min
      from station ) mm
where min = 1 or max = 1; 

내가 작성한 정답2 - limit

(select city, length(city) len from station order by 2, 1 limit 1)
union all
(select city, length(city) len from station order by 2 desc, 1 limit 1)

Oracle

내가 작성한 정답1 - row_number()

select city, len
from (select city, length(city) len 
            , row_number() over(order by length(city) desc, city asc) max
            , row_number() over(order by length(city), city) min
      from station ) mm
where min = 1 or max = 1; 

내가 작성한 정답2 - rownum

(select *
from (select city, length(city) len from station order by length(city), city)
where rownum = 1)
union all
(select *
from (select city, length(city) len from station order by length(city) desc, city)
where rownum = 1);

MS SQL Server

내가 작성한 정답1 - row_number()

select city, len
from (select city, len(city) len 
            , row_number() over(order by len(city) desc, city asc) max
            , row_number() over(order by len(city), city) min
      from station ) mm
where min = 1 or max = 1; 

내가 작성한 오답

(select top 1 city, len(city) len from station order by 2 , 1)
union all
(select top 1 city, len(city) len from station order by 2 desc, 1);

내가 작성한 정답2 - top 1

select city, len
from ((select top 1 city, len(city) len from station order by 2, 1)
        union all
      (select top 1 city, len(city) len from station order by 2 desc, 1))mm;

select city, len
from (select top 1 city, len(city) len from station order by 2, 1)mm
union all
select city, len
from (select top 1 city, len(city) len from station order by 2 desc, 1)m;

DB2

내가 작성한 정답1 - row_number()

with a as (select city, length(city) len,
                    row_number() over(order by length(city), city) min,
                    row_number() over(order by length(city) desc,city) max
            from station)
select city, len
from a 
where min = 1 or max = 1;

내가 작성한 정답2

(select city, length(city) len from station order by len, city limit 1)
union all
(select city, length(city) len from station order by len desc, city limit 1);

정리!