CODEWITHSUNDEEP
mysql
Mat Kay
Mat Kay

Reputation: 518

Which field is returned first when there is no order by clause?

In MYSQL which rows are returned first when there is no order by clause?

SELECT * FROM table;

Looking at phpmyadmin it seems to order my data with most recent data first, even though I don't have a field with a timestamp for that. What is the default sort and is it based on hidden fields?

/hopefully that is clear enough

Mat

Upvotes: 0

Views: 91

Answers (3)

paxdiablo
paxdiablo

Reputation: 881383

SQL itself does not specify the order that rows are returned in unless you have an order by clause, so you are well advised not to rely on such behaviour. Without an ordering clause, most DBMS' will almost certainly return rows in the order most convenient to them.

It may change if you switch to another DBMS. It may change if you switch to another version of MySQL. It may change as data is added to or removed from the database.

It may change depending on whether it uses an index or does a full table scan. The former case will probably have it sorted based on the index properties itself since that's how it would extract it. The latter case would be "sorted" in whatever order the rows happen to occupy in the main data area (i.e., effectively unsorted).

It may even change at random times of the month if the DBMS developer is a sadistic type, as most of them tend to be (a big hello to all my DB2 developer friends at Hursley).

Upvotes: 1

cdhowie
cdhowie

Reputation: 169008

If you do not specify an ORDER BY clause then the order of the returned rows is not defined; the database engine can return them in any order it pleases. This may be the order they were inserted in, or it may try to return rows in the order they appear on the disk to minimize seeking, or it may return them in a different, random order on each query. (Of course, any sane engine is unlikely to randomly order rows; this is an extreme example only to show that a database engine could do something bizarre like that and still be compliant with the SQL specification.)

Further, the order is not guaranteed to be the same the next time you issue the same query, nor is the behavior in this regard guaranteed to remain the same across different versions of the database engine. (Upgrading MySQL for example could change the order in which rows are returned!)

If you need the rows in a specific order you must use ORDER BY. Relying on the default ordering of rows is extremely fragile.

Further reading: What is the default order of records for a SELECT statement in MySQL?

Upvotes: 1

Bohemian
Bohemian

Reputation: 425003

There is no defined order of returned rows in any SQL database when there is no order by clause.

However it is usually first written first returned, simply because that's the order the rows were written to disk, but that can not be relied upon, because:

  1. The database is under no obligation to order the rows
  2. Early rows may be been deleted and the early disk pages reused by later inserted rows
  3. The database engine may have some rows cached in memory and decide to return those first

Upvotes: 1

Related Questions