Final Order of ORDERED subquery

Hi all<br /><br />I have quite a complicated stored proc with plenty of joins, and then orders it according to various parameters. The fields used by the ORDER BY, do not need to be in the final result set, so Im wrapping it all in a parent query which just picks the fields I need.<br /><br />Heres a simplified example..<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />select a.field1, a.field2 from <br />(<br /> select <br /> field1, field2, field3, field4 <br /> from <br /> table2 <br /> union all <br /> select <br /> field1, field2, field3, field4 <br /> from <br /> table3<br /> order by field4<br />) a<br /></font id="code"></pre id="code"><br /><br />My question is... since the parent query does not have an explicit order by (but the subquery DOES), is it safe to assume the final result set will be in the same order as the subquery ?<br /><br />Ive run some examples and I cant see anytime where they differ, but it would cause problems if one day they did differ<br /><br />Thanks, hope this isnt too confusing <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Paul<br /><br /><br /><br /><br /><br /><br /><br />

Thanks Frank, bad news is better than no news (I think <img src='/community/emoticons/emotion-1.gif' alt='' /> )

The simple answer is that resultsets are never ordered if there is no ORDER BY clause for the resultset. Doesn't matter if you're referring to a table or a derived table.

Without an ORDER BY, the rows may still come out in the order you wanted, but that is pure coincidence and may not be repeatable.

One thing that wasn't discussed: do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?

To answer my own question, "do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?"

... the answer would appear to be "no".

... but the order is again lost when you run a query on the ordered view that is JOINed to a table that is not included in the view itself.

quote:Originally posted by Adriaan

To answer my own question, "do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?"

... the answer would appear to be "no".

Q: do you still have to use an ORDER BY when querying a view that already contains the ORDER BY clause ?
A: Yes

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

I was afraid of that! So there is no point in using ORDER BY for the main query in a view definition? (And yes, I know it takes a TOP 100 PERCENT clause if you still want to use ORDER BY.)

The thing is, I was doing some tests querying a view that was ordered DESC in itself. The results appeared to come out in perfect DESC order, which only changed when I added another table to the query - at which point the order got messed up completely.

This is a breaking change in SQL server 2005, well, atleast for those who depend on the TOP 100 PERCENT workaround to Order in a view.<br /><br />Have a look at<br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms143179.aspx>http://msdn2.microsoft.com/en-us/library/ms143179.aspx</a><br /><br />and<a target="_blank" href=http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx>http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx</a><br /><br />(Frank, dont tell me that you are not subscribed to this blog [<img src='/community/emoticons/emotion-1.gif' alt='' />])<br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

Roji,

quote:In SQL Server 2005, the ORDER BY clause in a view definition is used only to determine the rows that are returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

As this is the list of "breaking changes", doesn't that imply that this was different in SQL 2000?

quote:Originally posted by Adriaan
As this is the list of "breaking changes", doesn't that imply that this was different in SQL 2000?

Yes. It used to work in 2000, even though it was never guaranteed. Now it breaks all the code that depended on this "feature"

You will be amazed by the number of people complaining a bout the "lost order" in the public groups

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

I would be surprised if I ever checked the public groups. I just never do.[<img src='/community/emoticons/emotion-4.gif' alt='' />]

It looked like it worked on sql server 2000, but actually it didn't always work. I can't find an example right now, but it was something like:


select *
from (select top 100 percent col1, ...
from table
order by col1 desc) as t

Mirko, that's when using a derived table. My question was about ordered views.

I am just guessing, but it may be that view is treated as derived table in query. Logicaly it is the same (if view is not indexed).