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

krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0410070213.21f8a373_at_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
You may want to study inline views. Basically they allow you to create a 'virtual table' on the fly (using all the traditional WHERE as well as ORDER BY, GROUP BY, JOINS, etc.) and use that 'virtual table' in the FROM clause.

There's a discussion about inline views in asktom, including some of the gotchas. Also the O'Reilly book, Mastering Oracle SQL provides excellent detail.

/Hans
/Hans Received on Thu Oct 07 2004 - 13:00:51 CDT