How to use sequence and ordered query?(s20)

I have a query which returns data sorted by some item using
the ORDER BY clause. Additionally I would like to assign an
increasing number to each row, so that the first row gets
the value x, the second row the value x+1. These numbers
should be applied to the sorted results, so that the row
with the lowest item also has the lowest assigned number.
This value should be stored in the database and should be
updated according to the amount of rows fetched, so the number
should be set to x+n after the query, if n rows were returned.

Such increasing numbers usually are achieved by using sequences.
Unfortunately sequences can't be used in ordered queries.
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.

I wonder whether anyone has a more elegant solution to this
problem?

bye
--
Sam Jordan


Ans1:

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.


Ans2:

Try using rownum in your query as in the following example

SQLWKS> select rownum+10,empno,ename
     2> from emp
     3>
ROWNUM+10  EMPNO      ENAME
---------- ---------- ----------
        11       7369 SMITH
        12       7499 ALLEN
        13       7521 WARD
        14       7566 JONES
        15       7654 MARTIN
        16       7698 BLAKE
        17       7782 CLARK
        18       7788 SCOTT
        19       7839 KING
        20       7844 TURNER
        21       7876 ADAMS
        22       7900 JAMES
        23       7902 FORD
        24       7934 MILLER
14 rows selected.
 

rownum is assigned as the rows are being returned from the database.

hope that helps

 tim l
 
Reply to Ans2:

... *before* any ordering is being done. So, bad luck, you can't use rownum.

Arjan.
 
 
 
 
 
 

Hosted by www.Geocities.ws

1