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