Xref: alice comp.databases.oracle.server:46744
Path: alice!news-feed.fnsi.net!newsxfer.visi.net!feeder.qis.net!news.idt.net!newspeer.monmouth.com!nntp2.dejanews.com!nnrp1.dejanews.com!not-for-mail
From: tlondon@my-dejanews.com
Newsgroups: comp.databases.oracle.server
Subject: Re: Sequence numbers / ordered queries
Date: Fri, 23 Apr 1999 12:35:13 GMT
Organization: Deja News - The Leader in Internet Discussion
Lines: 60
Message-ID: <7fppe2$mkh$1@nnrp1.dejanews.com>
References: <37204D46.2060CD6D@spin.ch>
X-Article-Creation-Date: Fri Apr 23 12:35:13 1999 GMT
X-Http-User-Agent: Mozilla/3.01Gold (WinNT; I)
X-Http-Proxy: 1.0 x10.dejanews.com:80 (Squid/1.1.22) for client 193.130.74.254

In article <37204D46.2060CD6D@spin.ch>,
  sjo@spin.ch wrote:
> Hi
>
> 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
>

  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

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own