| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sequence numbers / ordered queries
In article <37204D46.2060CD6D_at_spin.ch>,
sjo_at_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
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 Received on Fri Apr 23 1999 - 07:35:13 CDT
 |
 |