Add a sequence number for each element in a group using an Oracle SQL query

Question

I have an Oracle table with the following data:

ID          VALUE
10           A
10           B
10           C
20           A1
30           C1
30           D1

I want to do a group by based on the ID column and print a new column with the sequence number within each group.

The output would look like this:

ID          VALUE     GROUPSEQ
10           A           1
10           B           2
10           C           3
20           A1          1
30           C1          1
30           D1          2

Can this be done using an Oracle SQL query, without creating a temporary table?

Answer 1

You need ROW_NUMBER

SELECT ID, VALUE, row_number() OVER (PARTITION BY ID ORDER BY value) GROUPSEQ
FROM myTable