Re: renumbering the data in a column?
Date: Fri, 16 Jan 2009 09:52:01 -0800 (PST)
Message-ID: <3432c5aa-e207-4025-a6c8-a9b8396dbf2a_at_h5g2000yqh.googlegroups.com>
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..
>
> Thanks!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios
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)
If you need to change the processing order you could have the sequence increment by 2 so that holes exist to move lower ranked items into when you want to advance a lower ranked item.
Obviously there may be more to the process that your initial post and minor followup explain so I just want to make sure you have considered your options and are no doing unnecessary work.
HTH -- Mark D Powell -- Received on Fri Jan 16 2009 - 11:52:01 CST