How does mysql order rows with the same value?

Question

In my database I have some records where I am sorting by a column that contains identical values:

| col1 | timestamp              | | row1 | 2011-07-01 00:00:00    | | row2 | 2011-07-01 00:00:00    | | row3 | 2011-07-01 00:00:00    |  SELECT ... ORDER BY timestamp 

It looks like the result is in random order. Is the random order consistent? I have these data in two mysql servers can I expect the same result？

Answer 1

I'd advise against making that assumption. In standard SQL, anything not required by an explicit ORDER BY clause is implementation dependent.

I can't speak for MySQL, but on e.g. SQL Server, the output order for rows that are "equal" so far as the ORDER BY is concerned may vary every time the query is run - and could be influenced by practically anything (e.g. patch/service pack level of the server, workload, which pages are currently in the buffer pool, etc).

So if you need a specific order, the best thing you can do (both to guarantee it, and to document your query for future maintainers) is explicitly request the ordering you want.