SQL*Plus or Report style Break Groups in SQL Query
Rob just asked me if he could easily create break groups in a normal SQL query, somewhat like BREAK ON in SQL*Plus and break groups in Oracle Reports. The idea is that certain column values – Group Labels we can call them – are only printed for the first record in a group. If we select for example all employees from table EMP, grouped by DEPTNO, and we want to display the DEPTNO for the first Employee in that Department – how do we do that?
Using an Analytical Function – row_number – and an in-line view, this is quite simple. In the in-line view we select all employees, their name and deptno AND their row_number() within the group. In the wrapping query, we retrieve the Ename and – but only for the record with row_number equals 1 in the group – the Group Label, which is Deptno in this case:
select ename , case rn when 1 then deptno end deptno from ( select ename , deptno , row_number() over (partition by deptno order by ename) rn from emp ) /
The result of this query:
ENAME DEPTNO ---------- ---------- CLARK 10 KING MILLER ADAMS 20 FORD JONES SCOTT SMITH ALLEN 30 BLAKE JAMES MARTIN TURNER WARD 14 rows selected.
- 25-11-2004: KC SDPL over Query structuur
- Hierarchical query with nodes from different tables – DEPT and EMP nodes in one tree
- Pivoting in SQL using the 10g Model Clause
- Introduction to Analytical Functions – Use of Analytics as alternative for Simple Aggregations
- Analytical SQL Functions – theory and examples – Part 2 on the Order By and Windowing Clauses