23

Possible Duplicate:
Why do results from a SQL query not come back in the order I expect?

From reading 7.5 Sorting Rows and from issues I've seen with PostgreSQL, my impression is the following, but that section is not fully explicit, so I would be grateful if someone could verify:

SELECT * FROM items;

has no guaranteed order.

SELECT * FROM items ORDER BY published_date ASC;

guarantees that two items with different dates come in a given order, but does not guarantee that two items with the same date always come in the same order.

SELECT * FROM items ORDER BY published_date ASC, id ASC;

always returns items in the same order, since it is fully deterministic.

Do I have this right?

I'm not quite clear about whether sorting on one attribute (such as published_date) guarantees the order for records with the same value, as in the second example.

Community
  • 1
  • 1
Henrik N
  • 15,786
  • 5
  • 82
  • 131
  • 11
    You have it right. The only order that is guaranteed is the order that your "ORDER BY" imposes. If there are permutations possible *within* that order these could all be a valid output. – wildplasser Jun 29 '12 at 14:42
  • 1
    This is a specific case of the question: http://stackoverflow.com/questions/10999913/why-do-results-from-a-sql-query-not-come-back-in-the-order-i-expect – David Manheim Jun 29 '12 at 15:09

1 Answers1

19

Order is not guaranteed unless you explicitly specify it with the ORDER BY clause.

You might be getting data in the same order upon several executions in case there is no database activity, as PostgreSQL will just return rows in the order it finds them in the database pages. Do a small test:

  • insert a number of rows keeping the desired order;
  • query the table: you will get rows ordered;
  • update the very first record in the set;
  • query the table again;
  • observe the results.

In short: You might be even getting rows in the desired order, but this is just a coincidence.

vyegorov
  • 21,787
  • 7
  • 59
  • 73
  • A consistent eventual order is **not** a coincidence, since it's because of underlying data access and its inner organization. You're right though, that users cannot rely on it, because it can change for reasons completely unrelated to the application, as the creation of an index or database files relocation. – Gerardo Lima Jun 29 '12 at 14:58
  • Thank you! I read that manual page (linked it from the question), but I felt it was not explicit enough about this particular case. – Henrik N Jun 29 '12 at 15:21
  • Specifically, "A particular output ordering can only be guaranteed if the sort step is explicitly chosen." Not clear if "the sort step is explicitly chosen" by ordering on a non-unique column. – Henrik N Jun 29 '12 at 15:23
  • @HenrikN, As @wildplasser mentioned, all ordering variations within a non-unique set of columns in the `ORDER BY` are considered correct results. So documentation should be read: If you would like to _always_ receive data in the “right” order, you _should_ explicitly specify a set of unique columns in the `ORDER BY` clause. – vyegorov Jun 29 '12 at 15:32
  • 2
    @GerardoLima: Yes, it *is* just a coincidence. In PostgreSQL if you run exactly the same read-only query 1000 times while no changes are made to the database and there is no `ORDER BY` clause to force an ordering, you could get the same order *almost* every time, but have just a few executions produce a different ordering. One reason is that your query could be complex enough to use the "genetic query optimizer", which uses a heuristic to consider a random subset of plans. Or the query might use a sequential table scan; it will "join" a seqscan already in progress as an optimization. – kgrittn Jun 30 '12 at 18:23