How to get number sequence in Postgres for similar value of data in a particular column?

Question

I'm looking for an efficient approach where I can assign numbers in sequence to each group.

Record  Group     GroupSequence
-------|---------|--------------
1       Car       1
2       Car       2
3       Bike      1
4       Bus       1
5       Bus       2
6       Bus       3

I came through this question: How to add sequence number for groups in a SQL query without temp tables. But my use case is slightly different from it. Any ideas on how to accomplish this with a single query?

Answer 1

You are looking for row_number():

select t.*, row_number() over (partition by group order by record) as group_sequence
from t;

You can calculate this when you need it, so I see no reason to store it. However, you can update the values if you like:

update t
    set group_sequence = tt.new_group_sequence
    from (select t.*,
                 row_number() over (partition by group order by record) as new_group_sequence
          from t
         ) tt
    where tt.record = t.record;