Overview:

  • Oracle 23ai introduces 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 used to group rows
  • M: Specify how many different groups you want to return
  • Sort: A column or expression used to sort rows ASC|DESC
  • N: Specifies the first rows for each group returned

In this blog, I’ll show two demos demonstrating partition use by fetching the 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;

ai

 

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;

ai2