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?

Why you have tagged this ques with mysql and oracle both. Please remove mysql. — Ankit Bajpai
– Ankit Bajpai, Commented Sep 1, 2015 at 15:11

sstan · Accepted Answer · 2015-09-01 16:00:29Z

24

You need ROW_NUMBER

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

edited Sep 1, 2015 at 16:00

sstan

36.8k7 gold badges54 silver badges72 bronze badges

answered Sep 1, 2015 at 15:03

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

1 Comment

Victor Over a year ago

Great !! It Worked. Thx

Ankit Bajpai · Accepted Answer · 2015-09-01 15:12:09Z

2

You can try this:-

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

edited Sep 1, 2015 at 15:12

answered Sep 1, 2015 at 15:05

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

2 Comments

Rahul Over a year ago

Any specific reason this answer should be accepted over the other one which same and was given 3 minutes earlier than you?

Ankit Bajpai Over a year ago

I was saying because user doesn't select any of the answer as selected. If user selects other one. Then also i dont have any problem. Because selecting the answer help other people in future as well.

Satender K · Accepted Answer · 2015-09-01 15:16:47Z

1

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

answered Sep 1, 2015 at 15:16

Satender K

5813 silver badges14 bronze badges

5 Comments

Juan Carlos Oropeza Over a year ago

I think you want ORDER BY t.value

Satender K Over a year ago

No it should be order t.id

Juan Carlos Oropeza Over a year ago

if you order by t.id first group have 10 three time, so no really order

Satender K Over a year ago

what if the data into table is unordered

Juan Carlos Oropeza Over a year ago

You can see the OP result is order by value. if you order by id you can have any random result.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related