This discussion is archived
1 Reply Latest reply: Nov 7, 2012 3:51 AM by fac586 RSS

Is there any way to use Control Break in a SQL Query

DeepakJ Newbie
Currently Being Moderated
Hi,

Is there any way to use a control break on Dept column in a SQL query to have a Output-2 instead of Output-1.
Is there any way to modify the SQL query.

SQL
select dept, loc, count(*)
  from dept
group by dept, loc
Output-1
  Dept      Loc       Count(*)
  10         AA        1
  10         BB        2
  10         CC        2
  20         AA        2
  20         BB        2
Output-2
  Dept      Loc       Count(*)
  10         AA        1
             BB        2
             CC        2
  20         AA        2
             BB        2
Thanks,
Deepak
  • 1. Re: Is there any way to use Control Break in a SQL Query
    fac586 Guru
    Currently Being Moderated
    DeepakJ wrote:
    Hi,

    Is there any way to use a control break on Dept column in a SQL query to have a Output-2 instead of Output-1.
    Is there any way to modify the SQL query.

    SQL
    select dept, loc, count(*)
    from dept
    group by dept, loc
    Output-1
    Dept      Loc       Count(*)
    10         AA        1
    10         BB        2
    10         CC        2
    20         AA        2
    20         BB        2
    Output-2
    Dept      Loc       Count(*)
    10         AA        1
    BB        2
    CC        2
    20         AA        2
    BB        2
    Yes, using the <tt>lag</tt> analytic function and specified ordering of the data:
    select
        nullif(d.deptno, lag(d.deptno) over (order by d.deptno, d.loc, e.mgr nulls first)) deptno
      , nullif(d.loc, lag(d.loc) over (order by d.deptno, d.loc, e.mgr nulls first)) loc
      , e.mgr
      , count(*) n
    from
        dept d
          join emp e
            on d.deptno = e.deptno
    group by
        d.deptno
      , d.loc
      , e.mgr
    order by
        d.deptno
      , d.loc
      , e.mgr nulls first;
    
    DEPTNO  LOC       MGR   N
    ------  --------  ----  --
        10  NEW YORK         1 
                      7782   1 
                      7839   1 
        20  DALLAS    7566   2 
                      7788   1 
                      7839   1 
        30  CHICAGO   7698   4 
                      7839   1 
        40  BOSTON    7698   2 
                      7902   1 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points