#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Unhappy Number 2 precedes 1 in a record


    Good day!

    I have executed this query:

    SELECT * FROM company;

    and here's the output:

    id(integer) | name(text) | age(real) | address(text) | salary(real)
    2 | "Allen" | 25 | "Texas" | 15000
    1 "Paul" 32 "California" 20000
    3 "Teddy" 23 "Norway" 20000
    4 "Mark" 25 "Rich-mond" 65000
    5 "David" 27 "Texas" 85000
    6 "Kim" 22 "South-Hall" 45000
    7 "James" 24 "Houston" 10000


    I just want to know why is that number 2 precede number 1?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Rows in a relational database are NOT sorted.

    The order of the rows returned by a SELECT statement is undefined unless you use an ORDER BY statement. The database is free to choose whatever order it feels is most efficient unless an ORDER BY is used.

    The only way (and I mean: only as in: absolutely no other way) to get rows in a sorted manner is to use a ORDER BY clause on your select statement.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    The database is free to choose whatever order it feels is most efficient unless an ORDER BY is used.
    Indeed.
    In fact; if the query does not specify an order, then the records are not sorted at all. They are returned as they are found on disk. When records are updated they will be written elsewhere on the disk and thus the order in which they are found can change.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by Vinny42
    They are returned as they are found on disk.
    You can not even rely on that.
    If you have a join that's done through a hash join the order will again be different.
    If the query uses an index-only scan, the order again will be different
    If your query uses the same table scan as a different, concurrent query is using, the order again will be different (a query can "hop" on the table scan a different query is performing. If the "hop-on" happens after e.g. the first 100 rows, those 100 rows will be the last rows for the second query, but the first rows for the first query).
    There are probably even more examples on when the returned "order" will not be the "physical order".
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    You can not even rely on that.
    Perhaps I should have said: "the records will appear in whatever order the database processed them".

    It can and will change wihout warning and without any kind of predictability at all.

IMN logo majestic logo threadwatch logo seochat tools logo