Re: Update with a Repeating Sequence

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