SQL*Plus or Report style Break Groups in SQL Query

2

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.
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

2 Comments

  1. I just did a trace on the two different queries, and here’s the result: (actually I did the trace twice)
    ********************************************************************************

    select ename
    , case Row_Number() over (partition by deptno
    order by null
    )
    when 1
    then deptno
    end
    from t

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 2 166.66 286.72 0 124 0 0
    Execute 2 0.00 143.36 0 0 0 0
    Fetch 4 0.00 153.60 0 6 0 28
    ——- —— ——– ———- ———- ———- ———- ———-
    total 8 166.66 583.68 0 130 0 28

    Misses in library cache during parse: 2
    Optimizer goal: CHOOSE
    Parsing user id: 21

    Rows Row Source Operation
    ——- —————————————————
    14 WINDOW SORT
    14 TABLE ACCESS FULL T

    ********************************************************************************
    ********************************************************************************

    select ename
    , case rn
    when 1
    then deptno
    end deptno
    from ( select ename
    , deptno
    , row_number() over (partition by deptno order by null) rn
    from t
    )

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 2 0.01 215.04 0 5 0 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 6 0 28
    ——- —— ——– ———- ———- ———- ———- ———-
    total 8 0.01 215.04 0 11 0 28

    Misses in library cache during parse: 2
    Optimizer goal: CHOOSE
    Parsing user id: 21

    Rows Row Source Operation
    ——- —————————————————
    14 VIEW
    14 WINDOW SORT
    14 TABLE ACCESS FULL T

    ********************************************************************************

    I seems to me that the in-line view wins hands down!

  2. Nice! Analytical functions are soooo cool!

    You can also omit the in-line view and use the row_number() in the case-statement:

    SQL> explain plan for
      2  select ename
      3       , case Row_Number() over (partition by deptno
      4                                order by null
      5                            )
      6         when 1
      7         then deptno
      8         end
      9    from t
     10  /
    
    Explained.
    
    SQL> @xplan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------
    --------------------------------------------------------------------
    | Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |             |    14 |   126 |     4 |
    |   1 |  WINDOW SORT         |             |    14 |   126 |     4 |
    |   2 |   TABLE ACCESS FULL  | T           |    14 |   126 |     1 |
    --------------------------------------------------------------------
    
    Note: cpu costing is off
    
    

    Notice the Bytes, when compared to using an in-line view:

    SQL> explain plan for
      2  select ename
      3  ,      case rn
      4         when 1
      5         then deptno
      6         end  deptno
      7  from   ( select ename
      8           ,      deptno
      9           ,      row_number() over (partition by deptno order by null) rn
     10           from   t
     11         )
     12  /
    
    Explained.
    
    SQL> @xplan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------
    --------------------------------------------------------------------
    | Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |             |    14 |   462 |     4 |
    |   1 |  VIEW                |             |    14 |   462 |     4 |
    |   2 |   WINDOW SORT        |             |    14 |   126 |     4 |
    |   3 |    TABLE ACCESS FULL | T           |    14 |   126 |     1 |
    --------------------------------------------------------------------
    
    Note: cpu costing is off
    

    (I run these scripts on a Oracle 9205, in case you’re interested)