How to sort within a sql view

Question

I've created a sql view and I need to sort the results of select by using the ORDER BY on 4 fields, but I'm getting message that ORDER BY cannot be used in views unless I use TOP. Can someone explain why TOP is needed, and does someone have a workaround for sorting in a sql view?

Thanks.

Answer 1

If using SQL Server, you can do

select top 100 percent * 
from MyTable 
order by MyColumn

Of course you shouldn't use an * in your view, I just used it here for brevity.

Answer 2

you don't need to sort a view. a view is like a table so you sort it when you select from it:

select * from yourView order by yourColumns

Answer 3

There is no guarantee the output of the view will be ordered

Only the outermost ORDER BY applies for result sets: not any inner ones. So only this ORDER BY can be guaranteed to work:

SELECT col1, col2, FROm MyView ORDER BY col2

You can add it to views or derived tables and it forces "intermediate materialisation" because the results have to be ordered. However, for SQL Server 2005 and above, you have to use TOP 2000000000 not TOP 100 PERCENT (except for that HF that Daniel Vassallo mentioned!)

Someone will use your view with a different order to that internally at some point too.