Sort Order in Nested query

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Thu, 31 Jan 2013 17:03:09 +0000
Message-ID: <CAOuMUT5N8y5Z9p9tKCyaHJXbHApUr8ChHaVGvWy2KArJqnAxMg_at_mail.gmail.com>

Hi,
I have a query and I want the output ordered. I also want to assign a key to the output using a sequence. I don't care about the ordering in which the key is assigned.

*I can't do this:*

 select seq.nextval,USER_ID,CREATED from dba_users order by CREATED;

            *
ERROR at line 1:
ORA-02287: sequence number not allowed here

 *and I can't do this:*

select * from (select seq.nextval,USER_ID,CREATED from dba_users) order by CREATED

                          *

ERROR at line 1:
ORA-02287: sequence number not allowed here

*This seems to work*

select seq.nextval, v.* from (select USER_ID,CREATED from dba_users order by CREATED) v;

   NEXTVAL USER_ID CREATED

---------- ---------- ---------------
         1          5 29-DEC-06 20:17
         2          0 29-DEC-06 20:17
         3         11 29-DEC-06 20:18
         4         19 29-DEC-06 20:29
         5         21 29-DEC-06 20:46
         6         24 29-DEC-06 20:55
         7         25 29-DEC-06 21:00
         8         34 29-DEC-06 21:20
         9         35 29-DEC-06 21:21

*But can I rely on this always sorting on the created column (in different
versions)? Or is this just a fluke?*
*Is there a better way to do this?*
**
**
*If I have exclusive access to the target table I could do it without a
sequence using:*

    with v as (select max(id) as pmax from target)     select rownum+v.pmax as id , u.USER_ID, u.created     from dba_users u
    cross join
    v
    order by u.created;

        ID USER_ID CREATED

---------- ---------- ---------------
       122          5 29-DEC-06 20:17
       123          0 29-DEC-06 20:17
        63         11 29-DEC-06 20:18
        66         19 29-DEC-06 20:29
        67         21 29-DEC-06 20:46
       112         24 29-DEC-06 20:55
        56         25 29-DEC-06 21:00
        68         34 29-DEC-06 21:20
        58         35 29-DEC-06 21:21

Regards,
Fergal

.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 31 2013 - 18:03:09 CET