I think this gives what you want: CREATE TABLE mytest AS SELECT 1 trans_id, 'A' locat FROM DUAL UNION ALL SELECT 1 trans_id, 'C' locat FROM DUAL UNION ALL SELECT 22 trans_id, 'A' locat FROM DUAL UNION ALL SELECT 22 trans_id, 'B' locat FROM DUAL UNION ALL SELECT 22 trans_id, 'A' locat FROM DUAL UNION ALL SELECT 1 trans_id, 'C' locat FROM DUAL UNION ALL SELECT 1 trans_id, 'A' locat FROM DUAL;
SELECT ROW_NUMBER ()
OVER (PARTITION BY TO_CHAR (trans_id, '099999') || locat
ORDER BY (TO_CHAR (trans_id, '099999') || locat)) row_num,
trans_id,
locat
FROM mytest;
ROW_NUM TRANS_ID LOCAT
---------- ---------- -----
1 1 A
2 1 A
1 1 C
2 1 C
1 22 A
2 22 A
1 22 B
7 rows selected.
On Thu, Dec 2, 2010 at 7:11 AM, Gopakumar Pandarikkal <[email protected]
> wrote:
> 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 <[email protected]> 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 [email protected]
>> To unsubscribe from this group, send email to
>> [email protected]
>> 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 [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en<<plsqlgroups.GIF>>
