How to add sequence number for each element in a group using a SQL query without temp tables
12,835
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.
Author by
Admin
Updated on June 10, 2022Comments
-
Admin almost 2 years
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?
-
Chop over 6 yearsThe
PARTITION BY
was the missing link! Thanks!