Why we used ROWNUM in group by clause

[krishhhna]

If we want to retrive 5,6,7 rows from a table,then we used rownum in a query in group by clause,but my doubt is rownum has individual values for each rows then how did you group rows based on rownum?

[debasisdas]

Unlike ROWID ,ROWNUM is not fixed for every row. This is the serial number of the row in the recordset (cursor), not in the table. So ROWNUM is assigned after group by not before that.

[gintsp]

So ROWNUM is assigned after group by not before that.

Mhmm that's not true:

SQL> ed
Wrote file afiedt.buf
 
  1  select rownum, owner, count(*)
  2  from dba_source
  3  where rownum <=10
  4* group by rownum, owner
SQL> /
 
    ROWNUM OWNER                            COUNT(*)
---------- ------------------------------ ----------
         4 SYS                                     1
         6 SYS                                     1
         2 SYS                                     1
         3 SYS                                     1
         5 SYS                                     1
         8 SYS                                     1
         9 SYS                                     1
         7 SYS                                     1
         1 SYS                                     1
        10 SYS                                     1
 
10 rows selected.
 
SQL> ed
Wrote file afiedt.buf
 
  1  select rownum , owner, cnt from (
  2    select owner, count(*) cnt
  3    from dba_source
  4    where rownum <=10
  5    group by owner
  6* )
SQL> /
 
    ROWNUM OWNER                                 CNT
---------- ------------------------------ ----------
         1 SYS                                    10

If you need rownum for each group then you should do group by in inner select and assign rownum in outer.

Gints Plivna
http://www.gplivna.eu