4

If items are inserted in a table, and then I write a query, such as select * from table, why are the results not in the order that I expect?

David Manheim
  • 2,553
  • 2
  • 27
  • 42
  • Possible duplicate of [What is MySQL row order for "SELECT \* FROM table\_name;"?](https://stackoverflow.com/questions/1949641/what-is-mysql-row-order-for-select-from-table-name) – philipxy Jun 05 '19 at 03:25
  • @philipxy That question is RDBMS engine specific, even though it does not need to be, and won't be found by those searching more generally. (The more general question, https://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order , is not asking about why the order changed.) – David Manheim Jun 06 '19 at 08:29

2 Answers2

11

The order of a query can be forced by using an 'Order by' Clause in the statement. A SQL Database does not actually understand what order you put things in, or store the data in a given order. This means that you need to tell SQL what order you want the items in. For instance:

Select * from Table
  order by column1 desc

Think about it like handing some stuff to your friend to hold - she will have all of it for you later, but she stores it someplace in the mean time. She may move it around while you are not looking to make room for something else, or may hand it back in the same order you gave it to her, but you didn't tell her to keep it in order, so she doesn't.

Databases need to be able to move things around in the background, so the way they are built does not intrinsically know about any order - you need to know the order when you give it to the database, so that you can put it back in the order you want later. The order clause allows SQL to impose an order on the data, but it doesn't remember or have one on its own.

Important point: Even when SQL returned the items in the correct order without an order by statement the last 1 million times, it does not guarantee that it will do so. Even if a clustered index exists on the table, the results are not guaranteed to be returned in the order you expect. Especially when SQL versions change, not explicitly using an order by clause can break programs that assume the query will be in the order they want!

David Manheim
  • 2,553
  • 2
  • 27
  • 42
  • 3
    I like the "Important Point" - this is critical to understand if you want to avoid odd bugs in the future! – Simon Jun 12 '12 at 15:34
  • 1
    +1 - Good, concise answer. So many "order of insertion" questions lately. – Lamak Jun 12 '12 at 15:41
  • If we can start merging those into this question from now on, and not having good answers to the same question in a million places, that would be great! – David Manheim Jun 12 '12 at 15:42
  • If I understood this properly, then there can be some side effects: if I request the first 100 elements in a query without specifying any sort criteria, then I request the next 100, some rows can appear in both requests, as order is not guaranteed. Am I right? – Ignacio Segura Jan 23 '18 at 21:56
  • 1
    @IgnacioSegura - Correct, as long as they are two separate queries. – David Manheim Jan 24 '18 at 23:52
2

It is a common misconception to expect results in the order that is inserted. Even when a clustered index is used, the result set may not be as expected. Only way to force an order is to use an "order by" clause. The reason that the order is different from what is expected may vary. The query may be executed in parallel and the result set may be merged or it may be due to the query optimization plan while trying to return the result set as fast as possible.

user98534
  • 195
  • 3
  • 4
  • 9