Re: renumbering the data in a column?
From: joel garry <joel-garry_at_home.com>
Date: Mon, 19 Jan 2009 13:29:01 -0800 (PST)
Message-ID: <60feda0a-34bc-442b-9091-fe166ae95277_at_i24g2000prf.googlegroups.com>
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).
Date: Mon, 19 Jan 2009 13:29:01 -0800 (PST)
Message-ID: <60feda0a-34bc-442b-9091-fe166ae95277_at_i24g2000prf.googlegroups.com>
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?
jg
-- _at_home.com is bogus. http://catless.ncl.ac.uk/Risks/25.51.html#subj5Received on Mon Jan 19 2009 - 15:29:01 CST