Re: renumbering the data in a column?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 19 Jan 2009 22:58:20 +0100
Message-ID: <6tkbbsFbbn0cU1_at_mid.individual.net>
On 19.01.2009 22:29, joel garry wrote:
>> On 16.01.2009 18:52, Mark D Powell wrote:
>>
>>
>>
>>
>>
>>> On Jan 15, 6:39 pm, m..._at_pixar.com wrote:
>>>> Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>>>>> On Jan 14, 4:00 pm, ddf <orat..._at_msn.com> wrote:
>>>>>> You may need to modify the code for your actual data, but it,
>>>>>> hopefully, gives you a push in the right direction.
>>>> Thanks David, just what I needed!
>>>>> Mark, David's plan is fine providing the sequence is not a PK or UK
>>>>> referenced by a FK constraint, but why do you need to re-number the
>>>>> sequence column? If the sequence field is a key then generated key
>>>>> values are arbitrary in nature and gaps should not matter. The reason
>>>>> I am asking has more to do with how the value will be handled going
>>>>> forward rather than the 'fix' being made.
>>>> Ah, I should have explained that part. It's part of our
>>>> film processing system, the seq is actually a queue position.
>>>> So it's an easy way to bump an emergency job to the front
>>>> of the queue.
>>>> It's a PK, but processed items are deleted from the system
>>>> so there's always a gap at the front..
>>> Ok, but 15, 17, 26 became 1, 2, 3 so the relative order remained the
>>> same. If the queue logic processes in key order what is the real need
>>> to re-value the key? If you just want to show the relative queue
>>> position 1..n then you can do that in the query results via row_number
>>> or perhaps even using rownum of an ordered select, that is, select
>>> film, rownum as QueuePosition from (select film from table order by
>>> key)
>> Just guessing: maybe he is afraid of running out of sequence numbers or
>> wants to limit the sequence numbers because the fields has a small size
>> limit like NUMBER(4).
Date: Mon, 19 Jan 2009 22:58:20 +0100
Message-ID: <6tkbbsFbbn0cU1_at_mid.individual.net>
On 19.01.2009 22:29, joel garry wrote:
> On Jan 17, 2:15 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>> On 16.01.2009 18:52, Mark D Powell wrote:
>>
>>
>>
>>
>>
>>> On Jan 15, 6:39 pm, m..._at_pixar.com wrote:
>>>> Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>>>>> On Jan 14, 4:00 pm, ddf <orat..._at_msn.com> wrote:
>>>>>> You may need to modify the code for your actual data, but it,
>>>>>> hopefully, gives you a push in the right direction.
>>>> Thanks David, just what I needed!
>>>>> Mark, David's plan is fine providing the sequence is not a PK or UK
>>>>> referenced by a FK constraint, but why do you need to re-number the
>>>>> sequence column? If the sequence field is a key then generated key
>>>>> values are arbitrary in nature and gaps should not matter. The reason
>>>>> I am asking has more to do with how the value will be handled going
>>>>> forward rather than the 'fix' being made.
>>>> Ah, I should have explained that part. It's part of our
>>>> film processing system, the seq is actually a queue position.
>>>> So it's an easy way to bump an emergency job to the front
>>>> of the queue.
>>>> It's a PK, but processed items are deleted from the system
>>>> so there's always a gap at the front..
>>> Ok, but 15, 17, 26 became 1, 2, 3 so the relative order remained the
>>> same. If the queue logic processes in key order what is the real need
>>> to re-value the key? If you just want to show the relative queue
>>> position 1..n then you can do that in the query results via row_number
>>> or perhaps even using rownum of an ordered select, that is, select
>>> film, rownum as QueuePosition from (select film from table order by
>>> key)
>> Just guessing: maybe he is afraid of running out of sequence numbers or
>> wants to limit the sequence numbers because the fields has a small size
>> limit like NUMBER(4).
> > My guess is that it is to be human-readable - someone is going to be > looking at a list and saying "this one needs to go to the front." > What would you think of a top-ten list that had gaps in the numbers?
I'd think that in that case some numbers should repeat which is how proper RANKing works. ;-)
And also, of course, you can easier count them if the numbers do not have gaps.
Cheers
robert Received on Mon Jan 19 2009 - 15:58:20 CST