What Is the Default SQL Result Sort Order with 'Select *'

What is the default SQL result sort order with 'select *'?

There is NO default "sort" order. Rows in a relational table are not sorted.

The only (really: the only) way to get a specific order is to use an ORDER BY

Whatever order you see when running a SELECT without ORDER BY is pure coincident and can change with the next execution.

The order can change because of various reasons:

• other sessions are running the same statement
• the table was updated
• the execution plan changes
• ...

Here is a little SQLFiddle that shows you how the "order" can change: http://sqlfiddle.com/#!4/19d14/2

Note that the initial "order" is not even the same as the insertion order!

Default row order in SELECT query - SQL Server 2008 vs SQL 2012

You need to go back and add ORDER BY clauses to your code because without them the order is never guaranteed. You were "lucky" in the past that you always got the same order but it wasn't because SQL Server 2008 guaranteed it in anyway. It most likely had to do with your indexes or how the data was being stored on the disk.

If you moved to a new host when you upgraded the difference in hardware configuration alone could have changed the way your queries execute. Not to mention the fact that the new server would have recalculated statistics on the tables and the SQL Server 2012 query optimizer probably does things a bit differently than the one in SQL Server 2008.

It is a fallacy that you can rely on the order of a result set in SQL without explicitly stating the order you want it in. SQL results NEVER have an order you can rely on without using an ORDER BY clause. SQL is built around set theory. Query results are basically sets (or multi-sets).

Itzik Ben-Gan gives a good description of set theory in relation to SQL in his book Microsoft SQL Server 2012 T-SQL Fundamentals

Set theory, which originated with the mathematician Georg Cantor, is
one of the mathematical branches on which the relational model is
based. Cantor's definition of a set follows:

By a "set" we mean any collection M into a whole of definite, distinct
objects m (which are called the "elements" of M) of our perception or
of our thought. - Joseph W. Dauben and Georg Cantor (Princeton
University Press, 1990)

After a thorough explanation of the terms in the definition Itzik then goes on to say:

What Cantor's definition of a set leaves out is probably as important
as what it includes. Notice that the definition doesn't mention any
order among the set elements. The order in which set elements are
listed is not imporant. The formal notation for listing set elements
uses curly brackets: {a, b, c}. Because order has no relevance you can
express the same set as {b, a, c} or {b, c, a}. Jumping ahead to the
set of attributes (called columns in SQL) that make up the header of a
relation (called a table in SQL), an element is supposed to be
identified by name - not ordinal position. Similarly, consider the set
of tuples (called rows by SQL) that make up the body of the relation;
an element is identified by its key values - not by position. Many
programmers have a hard time adapting to the idea that, with respect
to querying tables, there is no order among the rows. In other words,
a query against a table can return rows in any order unless you
explicitly request that the data be sorted in a specific way, perhaps
for presentation purposes.

But regardless of the academic definition of a set even the implementation in SQL server has never guaranteed any order in the results. This MSDN blog post from 2005 by a member of the query optimizer team states that you should not rely on the order from intermediate operations at all.

The reordering rules can and will violate this assumption (and do so
when it is inconvenient to you, the developer ;). Please understand
that when we reorder operations to find a more efficient plan, we can
cause the ordering behavior to change for intermediate nodes in the
tree. If you’ve put an operation in the tree that assumes a
particular intermediate ordering, it can break.

This blog post by Conor Cunningham (Architect, SQL Server Core Engine) "No Seatbelt - Expecting Order without ORDER BY" is about SQL Server 2008. He has a table with 20k rows in it with a single index that appears to always return rows in the same order. Adding an ORDER BY to the query doesn't even change the execution plan, so it isn't like adding one in makes the query more expensive if the optimizer realizes it doesn't need it. But once he adds another 20k rows to the table suddenly the query plan changes and now it uses parallelism and the results are no longer ordered!

The hard part here is that there is no reasonable way for any external
user to know when a plan will change . The space of all plans is huge
and hurts your head to ponder. SQL Server's optimizer will change
plans, even for simple queries, if enough of the parameters change.
You may get lucky and not have a plan change, or you can just not
think about this problem and add an ORDER BY.

If you need more convincing just read these posts:

• Without ORDER BY, there is no default sort order. - Alexander Kuznetsov
• Order in the court! - Thomas Kyte
• Order of a Result Set in SQL - Timothy Wiseman

SQL best practice to deal with default sort order

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

how to sort order of LEFT JOIN in SQL query?

Try using MAX with a GROUP BY.

SELECT u.userName, MAX(c.carPrice)
FROM users u
    LEFT JOIN cars c ON u.id = c.belongsToUser
WHERE u.id = 4;
GROUP BY u.userName;

---

Further information on GROUP BY

The group by clause is used to split the selected records into groups based on unique combinations of the group by columns. This then allows us to use aggregate functions (eg. MAX, MIN, SUM, AVG, ...) that will be applied to each group of records in turn. The database will return a single result record for each grouping.

For example, if we have a set of records representing temperatures over time and location in a table like this:

Location   Time    Temperature
--------   ----    -----------
London     12:00          10.0
Bristol    12:00          12.0
Glasgow    12:00           5.0
London     13:00          14.0
Bristol    13:00          13.0
Glasgow    13:00           7.0
...

Then if we want to find the maximum temperature by location, then we need to split the temperature records into groupings, where each record in a particular group has the same location. We then want to find the maximum temperature of each group. The query to do this would be as follows:

SELECT Location, MAX(Temperature)
FROM Temperatures
GROUP BY Location;

Default row ordering for select query in oracle

According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'."

See this question at asktom.com.

As for ROWNUM, it doesn't physically exist, so it can't be "freed". ROWNUM is assigned after a record is retrieved from a table, which is why "WHERE ROWNUM = 5" will always fail to select any records.

@ammoQ: you might want to read this AskTom article on GROUP BY ordering. In short:

Does a Group By clause in an Query gaurantee that the output data will be
sorted on the Group By columns in
order, even if there is NO Order By
clause?

and we said...

ABSOLUTELY NOT,

It never has, it never did, it never
will.

what is the default order for a simple SELECT in a MySQL table with AUTO_INCREMENT column

From the DBA Stack Exchange site:

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

• So, to answer your question, MySQL sorts the records however it wants without any guarantee of consistency.
• If you are just curious about the internals of MySQL, Rolando provides an interesting answer.
• If, on the other hand, you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

Default sort my own order without sort by

Not sure what you mean by I know how to achieve the result using a temp table as you haven't shown this, however you could insert your values into a temp table / table variable with an Identity in the order you require the results, then inner join. This is likely the most performant option for larger result sets.

Note that if you want a result set in a specific order, you must specify an order by clause.

declare @filter table(seq int identity(1,1), id int)
insert into @filter values (28),(7),(5),(35)

select * 
from TheTable t
join @filter f on f.id = t.id
order by f.seq;

---

Related Topics