SQL is reversing the sort order on some queries, when no ORDER BY clause is provided
I have a table whose natural order is by its primary key id, so when I do
SELECT * from table
My result is ordered by id, ascending. However, every now and then, I will run a query on the table, without an ORDER BY clause, and the results are returned in order of id descending. Why is this happening? What would cause this, or what piece of information about how SQL works am I missing? What would determine the sort order of results when no ORDER BY clause is used? There are no JOINs or GROUP BYs happening either.
The table is maintained in such a way that the desired order of any result is in id order ascending so that time consuming ORDER BYs never have to be run.
Thanks for reading!
Without an order by the ordering of your result is not defined. It is returned in a for your database system optimal way, in a way unpredictable. So it is pure coincidence that your ids will be delivered descending. The ordering could be changed by rebuilding the index or inserting/removing data into your table.