A m Z o n e

Interesting Row numbering

To get row numbering in a select, ROWNUM can be used. How to number rows which are generated in a select statement with group by clause?

  E.g.: 
       SQL>select deptno, sum(sal) 
        2  from   emp 
        3  group by deptno; 

          DEPTNO   SUM(SAL) 
      __________ __________ 
              10       8750 
              20      10875 
              30       9400 

      I want an additional column in the above output, which shows row numbering like: 

          DEPTNO   SUM(SAL)         no. 
      __________ __________   _________ 
              10       8750           1  
              20      10875           2 
              30       9400           3 

Two solutions to this. Check these out:

1. Use In-line view**:

   
    
                   select deptno, sal, rownum 
                   from   (select deptno, sum(sal) sal 
                           from   emp 
                           group by deptno); 
   
   
   

2. 2. Use RANK( ) function:

   
   
                   select deptno, sum(sal), rank() over (order by deptno) as "no." 
                   from   emp 
                   group by deptno; 
   
   
   

Press the Back button of you Browser to go to previous page
Home