Re: Sequence numbers / ordered queries

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Fri, 23 Apr 1999 17:06:09 +0200
Message-ID: <7fq28l$5lu$1@weber.a2000.nl>

Sam Jordan wrote
> Unfortunately sequences can't be used in ordered queries.

True (well, I think the can be used, but the order might be different than you'd expected it to be), but what about

    select my_seq.next_val, t1.*
    from

        ( select *
          from my_table
          order by colA, colB, colC
        ) t1

Or, using rownum:

    select rownum, t1.*
    from

        ( select *
          from my_table
          order by colA, colB, colC
        ) t1

Note that you cannot use rownum directly in an ordered query, as rownum is assigned before any ordering or grouping is done.

> I didn't find a suitable way to achieve the same result with
> another technique, other than creating a new table containing
> this number and to select/update this value by hand.

What about a simple PL/SQL cursor loop?

    declare

        i number;
    begin

        select max(my_id)
            into i
            from my_table;
        for r in ( select * from my_table )
        loop
            i := i + 1;
            insert into my_other_table
                (my_id, colA, colB, colC)
            values
                (i, r.colA, r.colB, r.colC);
        end loop;
        commit;

    end;
    /

Arjan. Received on Fri Apr 23 1999 - 10:06:09 CDT