Sequence of rows for SQL with no ORDERBY
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Sequence of rows for SQL with no ORDERBY

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Sequence of rows for SQL with no ORDERBY

    Let us say the following query returning 5 rows.
    Code:
    SELECT USER_ID FROM CLG_USER WHERE USERNAME = 'DEV2FREESTU4'
    I tried running this a few times and everytime user_id = 100 is what is being returned first and that was the row last created by the application (we capture something like a create_date). Now, can I say that the row that was last created is the one that will be returned first if there is no ORDERBY in the SQL ?

    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Nope ... the order is essentially random and cannot be relied on.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The order is not random, but can change. The only way to guarantee an order is to use an ORDER BY.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "essentially random", in thesense that it might as well be, because the point at which the order will change at some future date is not known.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I suppose the correct word is "chaotic", since the order is deterministic but in practice unpredictable.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Wordplay

    I think it's safe to say that Jeff's second sentence is the standard Oracle reply,

    "The only way to guarantee an order is to use an ORDER BY"

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

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


Click Here to Expand Forum to Full Width