[HackerRank](E) Weather Observation Station 5

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

Weather Observation Station 5 | HackerRank

 

[문제]

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:

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths  and . The longest name is PQRS, but there are  options for shortest named city. Choose ABC, because it comes first alphabetically.

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

 

[답]

- oracle

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

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

- mysql

select city, length(city) from station
order by length(city), city
limit 1;


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

 

도시 이름과 이름의 글자수를 가장 짧은 도시, 긴 도시를 찾는 문제이다.

글자수가 같은 경우에는 알파벳 순으로 정렬을 해서 한개만 출력한다.

 

이 때, oracle와 mysql이 다른 점이 있다.

제한을 나타내는 함수인데 mysql, postgresql은 limit를 사용하고,

oracle는 rownum을 사용한다.

이 때, rownum을 select로 한 번 더 묶어서 조회하지 않으면 mysql의 limit한 결과와 다른 값이 출력된다.

 

참고 사항이 있는데, 글자수를 나타내는 함수가 mysql, oracle는 length(컬럼)이지만,

mssql은 len(컬럼)을 사용한다.