0

Here is my problem, I was asked to ensure that statements within a big project which are all wrapped like:

Select * from (Select ... order by column)

keep their ordering. This begs the question, is there some official source that defines that SQL select results keep their ordering?

To set this scenario into a grabbable yet minimal context:

Imagine you have a table with the name exampleTBL with columns A and B. You execute the statement:

Select * from (Select B from exampleTBL order by A);

will the results, independend from the used DBMS/Driver/Platform/... be ordered by A?

echo_Me
  • 37,078
  • 5
  • 58
  • 78
Sebastian van Wickern
  • 1,699
  • 3
  • 15
  • 31
  • No, the order is not guaranteed if you don't provide an `ORDER BY` (on the outermost query). The optimizer will decide the order for you otherwise. – Tim Schmelter Mar 06 '13 at 11:06
  • @TimSchmelter Would you have an official source on that? Not that I don't trust your statement, but i'd really like a source. – Sebastian van Wickern Mar 06 '13 at 11:08
  • I have not the time to search now, it depends on the rdbms anyway(although i think that at least Oracle and MS SQL-Server go together). – Tim Schmelter Mar 06 '13 at 11:12
  • @TimSchmelter I know that at least on Oracle the statement will remain ordered :) Thanks for your time, I'll try to hunt down an official source, if one exists. – Sebastian van Wickern Mar 06 '13 at 11:15
  • I'm not sure with Oracle, however, i don't think that the order is guaranteed if you don't provide an order. http://stackoverflow.com/a/3764548/284240 – Tim Schmelter Mar 06 '13 at 11:22

2 Answers2

1

No. Not every db supports ordering in subqueries so you should define the order in the outer query (or better yet just get rid of the outer query). Also if you want to ensure you're gonna get the same results you should include every column you're selecting in the order by. And finaly, order by on the outside doesn't change if the indices are used or not.

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • 1
    I disagree with the statement *if you want to ensure you're gonna get the same results you should include every column you're selecting in the order by*. If select from a single table and order by the primary key the order will always be the same, without using all fields. This is not to say the point of ordering by as many columns as necessary to maintain consistent results is not valid, but it is not necessary to order by all columns. – GarethD Mar 06 '13 at 11:40
  • You're right. It should be more like _In the ORDER BY include a column or a group of columns that guarantee they're unique across the result_ but I'm not sure how to write it to be easy to understand. – Jakub Kania Mar 06 '13 at 11:56
  • @JakubKania That's the definition of a candidate key. – Nick Larsen Sep 09 '13 at 18:03
1

From the SQL-2003 Standard (71WD2-02-Foundation-2011-12):

4.15.3 Derived tables

A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of an expression, such as a <joined table>, <data change delta table>, <query expression>, or <table expression>. A <query expression> can contain an optional . The ordering of the rows of the table specified by the <query expression> is guaranteed only for the <query expression> that immediately contains the <order by clause>.

Therefore there is no obligation for a DBMS to maintain the order by in a subquery. In your example just use:

Select B from (Select B, A from exampleTBL) t order by A;

I'd reccomend this approach regardless of whether or not your DBMS maintains the order.

GarethD
  • 68,045
  • 10
  • 83
  • 123