다음 중 아래 데이터 현황을 참고하여 결과가 다르게 나오는 쿼리인 것은?
---------------------------- 아 래 -----------------------------------------
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개 레코드가 선택된다