Re: ROWNUM is out of order when ORDER BY clause used

In article <3nr4ri$ik5_at_mother.usf.edu>, rife_at_aarlo.moffitt.usf.edu (Mike Rife) writes:
|> In article <3n13iv$12b6_at_rover.ucs.ualberta.ca>, jkoesvel_at_gpu.srv.ualberta.ca (Janice Koesveld) says:
|> >
|> >A user is trying to select records from a table in a specific order
|> >and would also like to number the rows retrieved using ROWNUM.
|> >Unfortunately, when he orders by a column, the ROWNUM is out of order.
|> >Is there some way to keep the sequence of ROWNUM and order by a table
|> >column?
|>
|> No. ROWNUM is determined before the ORDER BY CLAUSE is applied to the resulting records. You
|> can order by the ROWNUM by removing the ORDER BY CLAUSE or you can order by the column and
|> possibly lose your ROWNUM order.

Yes,

You can keep the ROWNUM in ORDER and still have the effect of ORDER BY if you do it in the following way,

1. Use GROUP BY in place of ORDER BY and create a view Take scott/tiger emp table. Say You want the list to be ordered by ename

	CREATE VIEW MY_EMP AS
		select ename, empno, job from emp group by ename, empno, job;

2) Now select from this view all the columns and ROWNUM. Then ROWNUM will be in ORDER.         SELECT ROWNUM, ename, empno, job from my_emp;

    ROWNUM ENAME EMPNO JOB
---------- ---------- ---------- ---------

         1 ADAMS            7876 CLERK
         2 ALLEN            7499 SALESMAN
         3 BLAKE            7698 MANAGER
         4 CLARK            7782 MANAGER
         5 FORD             7902 ANALYST
         6 JAMES            7900 CLERK
         7 JONES            7566 MANAGER
         8 KING             7839 PRESIDENT
         9 MARTIN           7654 SALESMAN
        10 MILLER           7934 CLERK
        11 SCOTT            7788 ANALYST
 
    ROWNUM ENAME           EMPNO JOB
---------- ---------- ---------- ---------
        12 SMITH            7369 CLERK
        13 TURNER           7844 SALESMAN
        14 WARD             7521 SALESMAN

If you observe the result it is ORDERED by ename and ROWNUM is also in ORDER.

But there is only one problem with this. If the result of your select contains duplicate rows GROUP BY eliminates the duplicate rows Received on Mon May 01 1995 - 00:00:00 CEST