SQL*Plus or Report style Break Groups in SQL Query

Lucas Jellema 2
0 0
Read Time:1 Minute, 1 Second

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.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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

I 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, […]
%d bloggers like this: