Whenever we want a query’s results sorted, you may think of using
the clause “order by.” You are correct: most of the
time, MySQL will return the results in expected order
with “order by.”
Be careful, though. Sometimes MySQL may return results in the
non-deterministic order even with “order by.”
Indeed, if a query is ordered by a non-unique column, it may
return results in an unexpected order. I tested the below example
on MySQL 5.1.73, 5.5.54 and 5.6.19 and got the same result.
However, when I applied the same example on MySQL 5.7.17, it
returned the results in an unexpected order differently.
Follow me step-by-step and see how MySQL returns results in a
non-deterministic order. Step 1-4 is for MySQL 5.1.73, 5.5.54 and
5.6.19, Step 5 is for MySQL 5.7.17. After the example, I will
explain the reason behind this output.
Step 1. Create the table as …[Read more]