Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    Unanswered: order by...........

    select ... from (select ... from tab order by 3,4,5)

    can i be 100% sure that the outer qry will output in the order as in the inner qry, without using an order by again.
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know - didn't try. Why don't you? Besides that, if you rewrite it this way:

    select ... from (select ... from tab) order by 3,4,5;

    you'll be sure and you'll still have only one order by.

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    but my req is like this

    select x.... from (select lag(a,1) over (order by c,d) x from tab)

    i need to do a lot with the x
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well,
    "select x.... from (select lag(a,1) over (order by c,d) x from tab)" and
    "select ... from (select ... from tab order by 3,4,5)"
    aren't the same. Unfortunatelly, I wouldn't know the answer to your question(s) ... sorry.

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Just throw an order by on the outside to ensure that it will actually get ordered correctly. As others suggested, the best way to test it to actually test! If you have a table with many rows, create another testing table that has maybe 15 rows. Then run your test on those 15 rows. You should be able to quickly see whether or not the inside query will maintain order.
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    ss659,

    for 15 rows... its working fine....but will it be the same for a many million records...
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    For an inline view, the order by on the outside will always be followed. Oracle processes the inside query first, and then the outside. So if your inside query was run in the correct order, and the outside query ran, and selected the same order, it will be the correct order.

    EX:
    Code:
    select 1,2,3
    from dual
    union all
    select 4,5,6
    from dual
    union all 
    select 7,8,9
    from dual
    order by 1 
    
    
    1     2     3
    4     5     6
    7     8     9
    EX: 2
    Code:
    select * from(
    select 1,2,3
    from dual
    union all
    select 4,5,6
    from dual
    union all 
    select 7,8,9
    from dual
    order by 1)
    order by 1 desc
    
    7     8     9
    4     5     6
    1     2     3

    EX3:
    Code:
    select * from(
    select 1,2,3
    from dual
    union all
    select 4,5,6
    from dual
    union all 
    select 7,8,9
    from dual
    order by 1)
    order by 1
    
    1     2     3
    4     5     6
    7     8     9
    So in summary, yes order by will ensure correct order if it is placed on the outside as well as inside
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •