Weather Observation Station 5

www.hackerrank.com/domains/sql

 

 

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.

Sample Output

ABC 3 PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths 3,3,4 and 3. The longest name is PQRS, but there are 3 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.

 

번역 : STATION 테이블에서 CITY의 글자수가 가장 작은것과 가장 많은것을 골라 내라. 조건은 같은 글자수가 있을 시 알파벳 순서대로 작은것 하나만 출력하기.

 

풀이 : 예를들어 ABC,DEF,PQRS가 있으면 ABC, PQRS만 나오게 하기!

 

MSSQL

 

꽤 어려웠다. 처음엔 이렇게만 했었는데, 그랬더니 ABC, DEF, PQRS 다 나와버렸다.

 

SELECT CITY, LEN(CITY)
         FROM STATION 
        WHERE LEN(CITY) = (SELECT MIN(LEN(CITY)) FROM STATION)
           OR LEN(CITY) = (SELECT MAX(LEN(CITY)) FROM STATION)

 

 

개선 시도 1.

WHERE 절에 SUBQUERY를 수정-> SUBQUERY에서 ORDER BY 후에 TOP1 만 추리기

: SUBQUERY에서는 ORDER BY 자체가 사용 불가

 

시도2.

UNION ALL 

: NOTE에서 SINGLE QUERY로만 작성하라고 했으니 PASS!

 

시도3. RANK로 각 컬럼별로 고유한 숫자를 줘서 TOP 1만 가져오기

: 결과적으론 성공적인 시도였다.

doorbw.tistory.com/221

[MS SQL Server] #10_순위 함수(RANK, DENSE_RANK, ROW_NUMBER, NTILE)

: RANK, DENSE_RANK, ROWNUMBER, NTITLE 다써봤지만 안되어서 PARTITION BY를 쓰니 해결!

 

짜라란~ 생각보다 길다..

EASY라며...WHY DID YOU SAY 'EASY' ?

SELECT DISTINCT T1.CITY , T1.LEN
FROM ( SELECT CITY, LEN(CITY) AS LEN, 
              RANK() OVER (PARTITION BY LEN(CITY) ORDER BY CITY ASC) AS RANK
         FROM STATION 
        WHERE LEN(CITY) = (SELECT MIN(LEN(CITY)) FROM STATION)
           OR LEN(CITY) = (SELECT MAX(LEN(CITY)) FROM STATION)
     ) AS T1
WHERE RANK = 1
ORDER BY LEN ASC

 

*

찾아보니 이런 방법도 있는듯?

HAVING은 잘안써서 생각도 안났는데 이런방법이!

SELECT MIN(CITY), LEN(CITY) 
FROM STATION
GROUP BY LEN(CITY)
HAVING LEN(CITY) IN 
       ((SELECT TOP 1 MIN(LEN(CITY)) FROM STATION GROUP BY LEN(CITY) ORDER BY LEN(CITY) DESC), 
        (SELECT TOP 1 MIN(LEN(CITY)) FROM STATION GROUP BY LEN(CITY) ORDER BY LEN(CITY)))