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

From: xtanto <krislioe_at_gmail.com>
Date: 7 Oct 2004 03:13:22 -0700
Message-ID: <e1c9bd55.0410070213.21f8a373@posting.google.com>

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

Received on Thu Oct 07 2004 - 05:13:22 CDT