Re: How to get Order Number from a query with ORDER BY ?

On 7 Oct 2004 03:13:22 -0700, krislioe_at_gmail.com (xtanto) wrote:

>Hi Gurus,
>
>We are developing a pension system where the oldest child of the
>employee is entitled to the pension, provided he/she is not more than
>25 years old.
>If the oldest child exceed 25 years old he will be removed from the
>system, the second oldest child replace him and so on.. so its
>rolling.
>
>So we maintain the children table. BUT I don't want to maintain the
>POSITION column, because I don't want to renumber the position when
>the oldest exceed 25 years.
>
>HOW Can I get the POSITION number on the fly using query statement ??
>(I have tried ROWNUM, but it gives me wrong order number)
>
>Thank you for your help,
>xtanto
>
>SQL SCript :
>CREATE TABLE CHILDREN
>(
> EMPNO NUMBER(4),
> CHILDNAME VARCHAR2(30),
> BIRTHDATE DATE
>);
>ALTER TABLE CHILDREN ADD
> CONSTRAINT PK_CHILDREN PRIMARY KEY (EMPNO, BIRTHDATE);
>
>Insert into CHILDREN values('5501', 'AAA', '12-JAN-1980');
>Insert into CHILDREN values('5501', 'CCC', '12-JAN-1985');
>Insert into CHILDREN values('5501', 'BBB', '12-JAN-1990');
>select rownum,children.* from children order by birthdate;
>RESULT :
>1,5501,AAA,1/12/1980
>3,5501,CCC,1/12/1985
>2,5501,BBB,1/12/1990
select x.*, rownum
from
(select *
 from children
 order by birthdate) x

looks like you are going to re-ask all the faqs of the last few years.

--
Sybrand Bakker, Senior Oracle DBA