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

Share on:

 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

Share on:

More from this Author

Oracle 23ai JSON Schema Support

Oracle 23ai: JSON Schema Support

Overview: Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data in Oracle 12.2 but without the option to ... Read More

Oracle 23ai Enumeration Domains – List of Values in the Database

Oracle 23ai: Enumeration Domains – List of Values in the Database

Overview: Oracle Database 23ai introduces a new way to create lists of values: enumeration (enum) domains Benefits: Create lists of name-value pairs ... Read More

Back to Top