I have some rows that I would like to renumber. For example:

select * from t where name like 'x%' order by seq;

seq name
--- ----
15 xfoo
17 xbar
26 xbaz

desired state, seq column renumbered starting at 1:

seq name
--- ----
1 xfoo
2 xbar
3 xbaz

Is there a single sql statement that can do this? Or should I
just loop over the result set and manually update the seq
for each row?

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

On Jan 14, 2:02*pm, m... (AT) pixar (DOT) com wrote:
This might get you started:

SQL> create table renum(
2 seq number,
3 name varchar2(10)
4 );

Table created.

SQL>
SQL> insert all
2 into renum
3 values(15,'xfoo')
4 into renum
5 values(17, 'xbar')
6 into renum
7 values(26, 'xbaz')
8 select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select seq, name, rank() over (order by seq) rk
2 from renum;

SEQ NAME RK
---------- ---------- ----------
15 xfoo 1
17 xbar 2
26 xbaz 3

SQL>
SQL> update renum
2 set seq = (select num.rk from
3 (select seq, rank() over (order by seq) rk
4 from renum) num
5 where num.seq = renum.seq)
6 /

3 rows updated.

SQL>
SQL> select *
2 from renum;

SEQ NAME
---------- ----------
1 xfoo
2 xbar
3 xbaz

SQL>

You may need to modify the code for your actual data, but it,
hopefully, gives you a push in the right direction.


David Fitzjarrell

On Jan 14, 4:00*pm, ddf <orat... (AT) msn (DOT) com> wrote:
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.

HTH -- Mark D Powell --

On 14 Jan, 20:02, m... (AT) pixar (DOT) com wrote:
I'm with Mark D Powell on this: why do you need to renumber that
column anyway?

-g

Mark D Powell <Mark.Powell (AT) eds (DOT) com> wrote:
Thanks David, just what I needed!

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

On Jan 15, 6:39*pm, m... (AT) pixar (DOT) com wrote:
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 --

On 16.01.2009 18:52, Mark D Powell wrote:
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).

Or use negative numbers for prepending.

Another approach could be to use a TIMESTAMP (insertion timestamp by
default) for ordering. Then no Oracle sequence would be needed and also
no overflow could happen (in the next 100 years or so anyway). Downside
is of course that a TIMESTAMP needs more space in the DB and does not
guarantee uniqueness (i.e. two insertions in the same moment -
millisecond or whatever the chosen TIMESTAMP precision is).

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end

On Jan 17, 2:15*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
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
--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/25.51.html#subj5

On 19.01.2009 22:29, joel garry wrote:
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