How to add sequence number for each element in a group using a SQL query without temp tables

Question

My question is quite similar to the one posted in this link - How to add sequence number for groups in a SQL query without temp tables

But, I need to enumerate the occurrence of group. The final output to be like this:

Record	Group	GroupSequence
1	Chickens	1
2	Chickens	2
3	Cows	1
4	Horses	1
5	Horses	2
6	Horses	3

Plus this has to be done in Oracle SQL. Any ideas?

Answer 1

Maybe something like this:

SELECT
    ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence1,
    RANK() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence2,
    DENSE_RANK() OVER(PARTITION BY [Group] ORDER BY Record) AS GroupSequence3,
    Table1.Group,
    Table1.Record
FROM
    Table1

GroupSequence1, GroupSequence2 and GroupSequence3 will get you the output you want.