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
WARNING: rownum does not do what you think it does. It's assigned BEFORE the ORDER BY clause so that's why you get the result you did. ROWNUM is something to be avoided. It's at best a beautifier and at worst just a cause of confusion.

try this in a cursor and only collect the first row: SELECT * FROM children
 WHERE birthdate<(sysdate-25years)
 ORDER BY birthdate;
details of how to enter the value for 25years is left as an exercise for the student. There's an even better way using MAX() and group by, but you should be able to firgure that once you get past this point.

And a final question for you: How does your policy handle twins?

  HTH,
  Ed Received on Thu Oct 07 2004 - 12:27:43 CDT