amar on web

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:

Use In-line view**:

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

2. Use RANK( ) function:

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

** Overhead issue in in-line view is a general opinion, which may not be seconded by Oracle. Some queries work faster with in-line views than any other conventional method. Sometimes it is the only option. I am yet to test this out, so wait to hear more from me. If you have any links, sites, info regarding overheads in in-line views, do pass it on.

Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was create on 15-dec-2000 and last updated on 27-dec-2006.
please forward all queries to amar_padhi@fastmail.fm