30

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?

mofoyoda
  • 695
  • 2
  • 10
  • 16

2 Answers2

54

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.

Community
  • 1
  • 1
Politank-Z
  • 3,653
  • 3
  • 24
  • 28
  • 1
    Thanks for PARTITION BY tip, but why do I need an ORDER BY gid ? – mofoyoda May 27 '15 at 20:14
  • 1
    Try it without. I assume you mean the `ORDER BY` in the `OVER` clause: without that, the DBMS doesn't know to count along with the `gid` column. It will "choose" an arbitrary order for its number within each `capt` partition, which, likelier than not, will not be what you're after. – Politank-Z May 27 '15 at 20:23
  • @mofoyoda: see [tutorial in the manual](http://www.postgresql.org/docs/current/static/tutorial-window.html) –  May 27 '15 at 20:50
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;