CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Victor
Victor

Reputation: 333

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

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?

Upvotes: 13

Views: 23739

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You need ROW_NUMBER

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

Upvotes: 24

Satender K
Satender K

Reputation: 581

I have written a query for you. I hope it will solve your problem :

(SELECT t.*,
    ROW_NUMBER ()
    OVER (PARTITION BY t.id
          ORDER BY t.id)
       seq_no
  FROM test t);

Check Fiddle

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try this:-

 SELECT ID, VALUE, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) "GROUPSEQ"
 FROM YOUR_TABLE;

Upvotes: 2

Related Questions