Ben,

If you are on 8.1.6 or higher, you can use the analytical function
ROW_NUMBER, partitioning by the column on which you want to reset the
sequence numbers. Following is an example against a table called FOO_RANK
where the numbers reset whenever the value in the RANK_ME column change. You
can use a similar technique in your create view statement.

SQL> desc foo_rank
Name Null? Type
----------------------------------------- -------- ------------------------
----
RANK_ME VARCHAR2(10)

SQL> l
1 SELECT rank_me,
2 ROW_NUMBER() OVER (PARTITION BY rank_me ORDER BY rank_me) as rm
3 FROM foo_rank
4* order by rank_me
SQL> /

RANK_ME RM
---------- ----------
misc1 1
misc1 2
misc1 3
misc1 4
misc2 1
misc2 2
misc3 1
misc3 2
misc3 3

Regards,

Larry G. Elkins
[Email Address Removed] -----Original Message-----
> From: Benjamin Bahrenburg [mailto:[Email Address Removed] Sent: Friday, June 29, 2001 3:32 PM
> To: LazyDBA.com Discussion
> Subject: View Question
>
>
> Hello:
> I am creating a view. I need a number sequenec that resets itself by
> groups.
> For Example:
>
> EMPNO(Group) SQ_NUM(number sequence)
> 1 1
> 1 2
> 1 3
> 2 1
> 3 1
> 4 1
> 4 2
>
> Any ideas
>
> Regards,
> Ben
>

Oracle LazyDBA home page