How to add numbers to grouped rows in postgresql group by clause
With help from this question and its answers:
SELECT gid, capt, row_number() OVER (PARTITION BY capt ORDER BY gid) AS rnumFROM 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.
This can be done using window functions:
select gid, capt, row_number() over (partition by capt order by gid) as rnumfrom the_tableorder by capt, gid;