By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

Why we used ROWNUM in group by clause

P: 13
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?
Oct 13 '07 #1
Share this Question
Share on Google+
2 Replies


debasisdas
Expert 5K+
P: 8,127
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.
Oct 13 '07 #2

P: 36
So ROWNUM is assigned after group by not before that.
Mhmm that's not true:
Expand|Select|Wrap|Line Numbers
  1. SQL> ed
  2. Wrote file afiedt.buf
  3.  
  4.   1  select rownum, owner, count(*)
  5.   2  from dba_source
  6.   3  where rownum <=10
  7.   4* group by rownum, owner
  8. SQL> /
  9.  
  10.     ROWNUM OWNER                            COUNT(*)
  11. ---------- ------------------------------ ----------
  12.          4 SYS                                     1
  13.          6 SYS                                     1
  14.          2 SYS                                     1
  15.          3 SYS                                     1
  16.          5 SYS                                     1
  17.          8 SYS                                     1
  18.          9 SYS                                     1
  19.          7 SYS                                     1
  20.          1 SYS                                     1
  21.         10 SYS                                     1
  22.  
  23. 10 rows selected.
  24.  
  25. SQL> ed
  26. Wrote file afiedt.buf
  27.  
  28.   1  select rownum , owner, cnt from (
  29.   2    select owner, count(*) cnt
  30.   3    from dba_source
  31.   4    where rownum <=10
  32.   5    group by owner
  33.   6* )
  34. SQL> /
  35.  
  36.     ROWNUM OWNER                                 CNT
  37. ---------- ------------------------------ ----------
  38.          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
Oct 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.