Oracle 12c / Top N rows, pagination

Top N Rows

In older versions, limiting sorted data by the number of rows had to be implemented using a nested query. This at first sorted the table, and only then was it possible to filter by count.

SELECT *
  FROM (
          SELECT last_name
               , salary
            FROM employees
            ORDER BY salary DESC
       )
  WHERE (ROWNUM<=2)
;

Oracle 12c incorporates the syntax of the ANSI/ISO SQL:2008 standard. You can use both the ROWS ONLY format, which returns the exact number of rows, and ROWS WITH TIES, where if subsequent rows after the last row contain duplicates, these are also returned.

SELECT last_name
     , salary
  FROM employees
  ORDER BY salary
  FETCH FIRST 2 ROWS ONLY
;

SELECT last_name
     , salary
  FROM employees
  ORDER BY salary
  FETCH FIRST 2 ROWS WITH TIES
;

Top N Percent of Rows

A similar construction can also be used for a percentage share of rows.

SELECT last_name
     , salary
  FROM employees
  ORDER BY salary
  FETCH FIRST 2 PERCENT ROWS ONLY
;

Pagination

An even less clear nested query was previously needed for pagination. First sorting, then limiting the upper bound, and finally limiting from the bottom.

SELECT last_name
     , salary
  FROM (
          SELECT e.*
               , ROWNUM AS start_from
            FROM (
                    SELECT *
                      FROM employees
                      ORDER BY salary
                 ) e
            WHERE (ROWNUM<=4)
       )
  WHERE (start_from>2)
;

The new approach is incomparably simpler. Both examples return the third and fourth employees with the lowest salary.

SELECT last_name
     , salary
  FROM employees
  ORDER BY salary
  OFFSET 2 ROWS
  FETCH NEXT 2 ROWS ONLY
;