Hi, inner order by will order only within the partition group just for rownumbering or ranking. so all C will come together. but I assume you have done an outer order by after the where clause that is why the split of "C" group happened and "D"'s came in the middle.
anyways as you need the out put as given in the second image we have to do some round about control break mechanism. I did a similar one using the hr schema of oracle expresss edition. I had to use a UDF and a sequnce. using variables in UDF also may work. [image: plsqlgroups.GIF] This has a similar situation you have described. 60 is appearing in the middle of 90 I use lag() analytical function in the inner queries to get the previous row value of department. it work like a control break mechanism. in the outer query I am calling udf with lag and dep(previous row departmnet and current row department.) so if they are different then it means the group is changing. then next value from sequence is taken(for numbering each group). if they are equal then currval is taken(same groupnumber). in the next outer query I am using rownumber with this newgroupnumber and deptid as partition.(now I thing only newgroupid is enough for partition) see next 90 group got a different numbering. I tried without UDF(case when lg <> dp then myseq.nextval else myseq.currval end). but nextval and currval wont work in the same sql. they work just as nextval only. Regards Gopa On Wed, Dec 1, 2010 at 7:53 PM, Iyad Bacdounes <iya...@gmail.com> wrote: > > > Hello, > > I applied a ROW_NUMBER analytic function as follow: > > ROW_NUMBER( ) OVER (PARTITION BY TRANS_ID, LOCATION ORDER BY ID ) > > so it gives me the following result > Please note that location 'C' row_num col > the row_num continued even when where are location 'D' in the meddle > > [image: aaA.bmp] > > > but I need to get a sequence to each part alone just like the follow: > > Please notice the 'C' is starting a new sequence when 'D' in the meddle > > [image: BBB.bmp] > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- Gopakumar P.G. -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
<<plsqlgroups.GIF>>
