[SQLD 퀴즈] 윈도우 함수 - 순위함수(2)

다음 중 아래 데이터 현황을 참고하여 결과가 다르게 나오는 쿼리인 것은?

 

---------------------------- 아       래 -----------------------------------------

 

select  EmployeeID, LastName, HireDate, Country
from    Employees
order by HireDate
 
*******************************************************************************

EmployeeID

-----------

3

1

2

4

5

6

7

8

9

LastName

------------

Leverling

Davolio

Fuller

Peacock

Buchanan

Suyama

King

Callahan

Dodsworth

HireDate

--------------------

1992-04-01 00:00:00.000

1992-05-01 00:00:00.000

1992-08-14 00:00:00.000

1993-05-03 00:00:00.000

1993-10-17 00:00:00.000

1993-10-17 00:00:00.000

1994-01-02 00:00:00.000

1994-03-05 00:00:00.000

1994-11-15 00:00:00.000

Country

--------

USA

USA

USA

USA

UK

UK

UK

USA

UK

 

--------------------------------------------------------------------------------

 

①  select  EmployeeID, LastName, HireDate, Country
    from   (select  EmployeeID, LastName, HireDate, Country
                  , rank () over (order by HireDate) as rnum
            from    Employees
           ) a
    where   a.rnum <= 5
    order by EmployeeID

 

②  select  EmployeeID, LastName, HireDate, Country
    from   (select  EmployeeID, LastName, HireDate, Country
                  , row_number() over (order by HireDate) as rnum
            from    Employees
           ) a
    where   a.rnum <= 5
    order by EmployeeID

 

③  select  EmployeeID, LastName, HireDate, Country
    from    Employees a
    where   HireDate in (select top 5 HireDate
                         from   Employees
                         order by HireDate )
    order by EmployeeID

 

④ select  EmployeeID, LastName, HireDate, Country
   from   (select  EmployeeID, LastName, HireDate, Country
                 , dense_rank() over (order by HireDate) as rnum
           from    Employees
          ) a
   where   a.rnum <= 5
   order by EmployeeID

 

 

[출처] http://www.dbguide.net/da.db?cmd=snb13_view&boardGroupUid=6&boardConfigUid=81&boardUid=152639

 

 

 

 

 

* 정답 및 해설 *

 

정답 : ② 


1, 4번은 5번째와 6번째 레코드가 rnum으로 똑같이 5를 부여받기 때문에 HireDate 순으로 6개 레코드가 선택된다. 참고로, 7번째 레코드는 각각 7과 6을 부여받는다.


3번은 서브쿼리에서 5개 레코드가 선택되지만, 메인쿼리와 조인하고 나면 최종적으로 6개 레코드가 선택된다.


2번은 HireDate 순으로 5개 레코드가 선택된다