How to add numbers to grouped rows in postgresql group by clause

Question

I have a PostgreSQL table, containing duplicate values in a text column. It looks like this:

gid, capt
1, foo
2, foo
3, bar
4, bar
5, bar
6, buz
7, buz

I need to add a column with distinct numbers for each value in a group, so my table should look like this:

gid, capt, rnum
1, foo, 1
2, foo, 2
3, bar, 1
4, bar, 2
5, bar, 3
6, buz, 1
7, buz, 2

It's a kind of a row number inside each group, always starting from 1. Can anyone please provide me with an appropriate SELECT statement?

Answer 1

With help from this question and its answers:

SELECT gid, capt, row_number() OVER (PARTITION BY capt ORDER BY gid) AS rnum
FROM your_table_here ORDER BY gid;

The row_number window function provides the count.

The PARTITION BY statement in the OVER clause tells the database to restart its numbering with each change to capt. The ORDER BY in the OVER clause tells the database to count along with the gid column.

Answer 2

This can be done using window functions:

select gid, 
       capt, 
       row_number() over (partition by capt order by gid) as rnum
from the_table
order by capt, gid;