CODEWITHSUNDEEP
sqlpostgresql
Hugh_Kelley
Hugh_Kelley

Reputation: 1040

Generate Series within Group

If I have data like:

pkey, category, group_id
1,       a,      NULL
2,       a,      NULL
3,       a,      NULL
4,       b,      NULL
5,       b,      NULL
6,       b,      NULL

how can I replace the nulls with a serial that resets for each category?

to get:

pkey, category, group_id
1,       a,      1
2,       a,      2
3,       a,      3
4,       b,      1
5,       b,      2
6,       b,      3

Thanks

Upvotes: 0

Views: 335

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You could use ROW_NUMBER:

SELECT
    pkey,
    category,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY pkey) group_id
FROM yourTable
ORDER BY
    pkey;

screen capture from demo link below

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use row_number():

select t.*, row_number() over (partition by category order by pkey) as group_id
from t;

You can include this in an update if you actually want to change the data:

update t
    set group_id = tt.new_group_id
    from (select t.*, row_number() over (partition by category order by pkey) as new_group_id
          from t
         ) tt
    where tt.pkey = t.pkey;

Upvotes: 1

Related Questions