Add count/instance no. column to table (SQL Developer)

Question

I have a table with the columns car_id, stop_location, arrive_time and leave_time. I'd like to add a column stop_sequence, which would start at 1 and increment by 1 for each row with the same car_id and then start again at 1 for the next car_id (when ordered by car_id, arrive_time).

It should look like this:

car_id    stop_lcn    arr_time    leave_time    stop_sequence

A         Home        --          0900          1
A         Shop        1000        1100          2
A         Work        1130        1700          3
B         Home        --          --            1
C         Home        --          0900          1
C         School      0930        1600          2

If anybody can tell me how I would go about adding this extra column I'd greatly appreciate the help!

Why do you need to add a column? You can calculate stop_sequence as `row_number() over (partition by car_id order by leave_time) as stop_sequence` — Lennart - Slava Ukraini, Jun 25 '14 at 20:14
Oh that's great! I didn't know that query... I still need to add the column because somebody else needs the table for a procedure that requires the 'stop_sequence' column. — Pete Ludlow, Jun 25 '14 at 20:19
I would let the other person know how to calculate it instead of adding the column. It can make the data not make sense with itself. Deleting or inserting a row for the existing groups would throw the sequence off. Just something to think about. — SQLChao, Jun 25 '14 at 20:22
You probably have good reasons for what you are doing, I just want to point out the possibility to add a view over the table: `create view ... as select car_id, stop_lcn, arr_time, leave_time, row_number() over (partition by car_id order by leave_time) as stop_sequence from T`. Now the procedure can use the view. — Lennart - Slava Ukraini, Jun 25 '14 at 20:25

score 1 · Accepted Answer · answered Jun 25 '14 at 21:01

Just use the analytic functions:

select c.*,
       row_number() over (partition by car_id order by leave_time) as stop_sequence
from cars c;

Actually, after writing this, I see than @Lennart has given this answer in the comments. If that person answers the question, I will delete this answer.

Add count/instance no. column to table (SQL Developer)

1 Answers1