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?

Arion · Accepted Answer · 2012-04-20 10:47:42Z

13

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.

edited Apr 20, 2012 at 10:47

answered Apr 20, 2012 at 10:42

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related