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
ROWNUM is assigned as the rows are returned to the result set, not when they are ordered, so your query is not going to produce the correct results. See below for a correct approach to this:

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', '12-JAN-1980');

1 row created.

SQL> Insert into CHILDREN values('5501', 'CCC', '12-JAN-1985');

1 row created.

SQL> Insert into CHILDREN values('5501', 'BBB', '12-JAN-1990');

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 order by birthdate;

        RN BIRTHDATE

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

SQL> Another approach is to use your query, minus the rownum, as an in-line view:

SQL> select rownum, c.birthdate
  2> from (select birthdate from children where empno = 5501 order by birthdate) c;

    ROWNUM BIRTHDATE

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

SQL> Either approach returns the desired results.

David Fitzjarrell Received on Thu Oct 07 2004 - 09:58:24 CDT