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.
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!
Nice! Analytical functions are soooo cool!
You can also omit the in-line view and use the row_number() in the case-statement:
Notice the Bytes, when compared to using an in-line view:
(I run these scripts on a Oracle 9205, in case you’re interested)