Oracle SQL Row Limiting Clause

針對Oracle Database Server，如果我們要達到Top-N查詢的效果，在Oracle Database 11g 版本之前，我們只能透過Inline View搭配rownum的效果操作，例如：

這隻SQL Statement將會回傳公司薪水最高的前5名員工資訊

select * from
(select employee_id,salary from employees order by salary desc)
where rownum <=5;

然而這樣的SQL 程式碼只能用在Oracle Database Server，如果同樣的語法在MS SQL Server上將不能執行成功，因為這樣的語法並不符合ANSI SQL的標準，它只是Oracle Database Server專屬的語法。

除此之外，在Oracle Database 11g 版本之前的做法，針對要求得薪水由高到低排名第6到第10名的這種Top-N查詢的需求，甚至要使用到集合運算子(SET Operators)，例如：

select * from (select employee_id,salary from employees order by salary desc)
where rownum <=10
MINUS
select * from (select employee_id,salary from employees order by salary desc)
where rownum <=5;

好消息是，從Oracle Database 12c R1版本開始，我們可以使用row_limiting_clause 來開發符合ANSI SQL標準的Top-N查詢語法，語法如下：

SELECT …FROM …

[ WHERE … ]

[ ORDER BY … ]

[OFFSET offset { ROW | ROWS }]

[FETCH { FIRST | NEXT } [{ row_count | percent PERCENT }] { ROW | ROWS }

{ ONLY | WITH TIES }]

所以我們現在將上述兩個範例以使用row_limiting_clause的方式改寫，如下：

–求得公司薪水最高的前5名員工資訊
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
FETCH NEXT 10 ROW ONLY;

–求得薪水由高到低排名第6到第10名的員工資訊
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY;

補充說明：

(1)使用ROW or ROWS的效果是一樣的

(2)使用FIRST or NEXT的效果是一樣的

(3)–假設第7跟第8名是相同值，此時應該要使用WITH TIES取代ONLY)
SELECT employee_id,last_name,department_id,salary FROM employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;