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!

Answers


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.


Need Your Help

Large free block of english non-pronoun text

text seed

As part of teaching myself python I've written a script which allows a user to play hangman. At the moment, the hangman word to be guessed is simply entered manually at the start of the script's co...

How can I do to reload a symfony2 server with gulp?

php symfony gulp gulp-watch browser-sync

I would like to reload automatically my server symfony2 to see in real time the changes on my html pages, when I do some change with gulp,