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

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 7 Oct 2004 08:31:42 -0700
Message-ID: <9711ade0.0410070731.1f2f980a@posting.google.com>

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
In my desire to provide a solution I completely forgot the condition of age being less than or equal to 25 years. This, of course, adds some additional complexity to the query, however it is not an impossible task:

SQL> CREATE TABLE CHILDREN
  2 (
  3 EMPNO NUMBER(4),
  4 CHILDNAME VARCHAR2(30),
  5 BIRTHDATE DATE
  6 );

Table created.

SQL> ALTER TABLE CHILDREN ADD
  2> CONSTRAINT PK_CHILDREN PRIMARY KEY (EMPNO, BIRTHDATE); Table altered.

SQL> Insert into CHILDREN values('5501', 'AAA', to_date('12-JAN-1980', 'DD-MON-YYYY')); 1 row created.

SQL> Insert into CHILDREN values('5501', 'CCC', to_date('12-JAN-1985', 'DD-MON-YYYY')); 1 row created.

SQL> Insert into CHILDREN values('5501', 'BBB', to_date('12-JAN-1990', 'DD-MON-YYYY')); 1 row created.

SQL> commit;

Commit complete.

SQL> select row_number() over (order by birthdate) as rn, birthdate   2 from children
  3 where empno = 5501
  4 and trunc(sysdate) < add_months(birthdate, 312)   4 order by birthdate;

        RN BIRTHDATE

---------- ---------
         1 12-JAN-80
         2 12-JAN-85
         3 12-JAN-90

SQL> update children set birthdate = add_months(birthdate, -60);

3 rows updated.

SQL> commit;

Commit complete.

SQL> select row_number() over (order by birthdate) as rn, birthdate
  2> from children
  3> where empno = 5501
  4> and trunc(sysdate) < add_months(birthdate, 312)
  5> order by birthdate;

        RN BIRTHDATE
---------- ---------
         1 12-JAN-80
         2 12-JAN-85

SQL> Now, to finish off this query:

SQL> select birthdate from
  2> (select row_number() over (order by birthdate) as rn, birthdate
  3> from children
  4> where empno = 5501
  5> and trunc(sysdate) < add_months(birthdate, 312)
  6> order by birthdate)
  7> where rn = 1;

BIRTHDATE

---

12-JAN-80 SQL> This should return the oldest child aged 25 years or less. Since, if I read this correctly, you are considering the entire 25th year as being eligible for pension payout, you are looking for the child who has not yet attained the age of 26 years, hence the 312 in the add_months() function. Using add_months() should correct for any leap years that occur within the given period. I realised this as I was modifying my original post to provide you only one row of output.

I hope this helps.

David Fitzjarrell Received on Thu Oct 07 2004 - 10:31:42 CDT