SQL*Plus or Report style Break Groups in SQL Query

Lucas Jellema 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.

2 thoughts on “SQL*Plus or Report style Break Groups in SQL Query

  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)

Comments are closed.

Next Post

JoSQL

Facebook0TwitterLinkedinI just read an announcement of a new release of JoSQL, SQL for Java Objects. My first reaction was a bit reserved but after a quick glance at their website I am quite impressed already. JoSQL provides functionality to use SQL statements to query a collection of Java Objects (Strings, […]