[SQL문제풀기] Weather Observation Station 5

silver

Apr 03, 2025

Contents

문제 MySQL Oracle MS SQL Server DB2 정리!

문제

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

내가 작성한 오답

: group by로 묶지 않아서 min과 max사용이 안된다는데 …. 왜죠? 여기서는 전체에서 최대와 최소를 사용해야 하기 때문에 그냥 min과 max를 사용해야하는데….


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()

: mysql과 동일


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()

: length → len


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;

내가 작성한 오답

: SQL Server에서 ORDER BY를 사용할 때 UNION ALL을 적용하려면 서브쿼리를 활용해야 한다.


(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);

정리!

💡

DBMS	첫번째 행 선택	길이 추출 메서드
MS SQL Server	TOP 1	LEN
MySQL	LIMIT 1	LENGTH
PostgreSQL	LIMIT 1	LENGTH
Oracle	WHERE ROWNUM <= 1	LENGTH
DB2	LIMIT 1	LENGTH

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;

(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);

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;

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;