database - 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.