Oracle 23ai: Fetch Top-N Rows Per Group Queries

 

- Overview:

• Oracle 23ai introduces the use of partition by clause in fetch first clause to get top-N rows per group.
• A query's syntax

          SELECT ........
          FROM    ........

          ORDER BY <group>, <sort>

          FETCH FIRST <M> <group>, <N> ROWS ONLY

           Where:

           - group: a column or expression that will be used to group rows.

           - M: specifies how many different groups you want to return.

           - sort: a column or expression that will be used to sort rows ASC|DESC.

           - N: specifies the first rows for each group returned.

   In this blog, I'll show two demos demonstrating the use of partition by in fetch first clause to get top-N rows per group.

- Prerequisites:

• Oracle Database 23ai.

Demo #1

- Fetch the two highest paid employees for the first three departments.

   Where:

    - group: column departments.department_id.

    - M: 3.

    - sort: column employees.salary DESC.

    - N: 2.

SELECT department_id,department_name, salary, first_name, last_name

FROM employees join DEPARTMENTS USING (DEPARTMENT_ID)

ORDER BY department_id, salary DESC

FETCH FIRST

      3 PARTITION by department_id,

      2 ROWS ONLY;

Demo #2

- Fetch the latest hired employee in each department. 

   Where:

    - group: column departments.department_id.

    - M: set to a large value. For example, 10000000.

    - sort: column employees.hire_date DESC.

    - N: 1.

SELECT DEPARTMENT_ID, HIRE_DATE, first_name, last_name

FROM employees 

ORDER BY DEPARTMENT_ID, HIRE_DATE DESC

FETCH FIRST

      999999999999 PARTITION by DEPARTMENT_ID,

      2 ROWS ONLY;