Bill Thoen wrote:
Steve Atkins wrote:
On Oct 14, 2008, at 9:04 AM, Bill Thoen wrote:
I've got a table with repeated records that I want to make unique by
adding a sequence code of 0,1,2,...,n for each set of repeated
records. Basically, I want to turn:
field_id | seq
----------+-----
1 | 0
2 | 0
3 | 0
3 | 0
3 | 0
4 | 0
4 | 0
5 | 0
6 | 0
into:
field_id | seq
----------+-----
1 | 0
2 | 0
3 | 0
3 | 1
3 | 2
4 | 0
4 | 1
5 | 0
6 | 0
What's the best way to that?
This is mildly tricky to do, and hard to maintain.
In most cases where people say they need this, they're actually
perfectly happy with the seq value being enough to make the row
unique, and ideally increasing in order of something such as insertion
time ...
I know its academic now. But this is a great use case for the windowing
functions being added to 8.4. In 8.4 it should be as easy as
SELECT field_id, RANK() OVER(PARTITION BY field_id) AS seq
FROM foo;
Artacus
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
