View 'order by' not working

Question

Hi there,

I have a view created using the following code.  The view works perfectly but does not order by the name column as I've asked it to do.  In the view designer if I click on execute then the order is applied but if I save the view and run it externally (i.e. in an ASP page or within the management terminal) it does not order correctly and seems to order by the Id column.

Any help would be much appreciated.  Here's the code:

SELECT     TOP (100) PERCENT dbo.Members.DivisionID, COUNT(*) AS Members, dbo.Country.Name
FROM         dbo.Members INNER JOIN
                      dbo.Country ON dbo.Members.DivisionID = dbo.Country.CountryID AND dbo.Members.CountryID = dbo.Country.CountryID
GROUP BY dbo.Members.DivisionID, dbo.Country.Name
ORDER BY dbo.Country.Name

And another, more simpler view, that doesn't sort as it's supposed to.

SELECT     TOP (100) PERCENT CountryID, Name, RegionID, IsActive, HasFlag, URL, Comments
FROM         dbo.Country
ORDER BY Name

Many thanks,

Ady

Answer 1

hi,

in my experience, even if not correct, it works (even if not expected )..

USE Pubs;
GO
CREATE VIEW dbo.authRevers 
AS 
 SELECT TOP(100) PERCENT au_lname, au_fname, phone, address
  FROM dbo.authors 
  ORDER BY au_lname DESC;
GO
SELECT * FROM dbo.authRevers;
DROP VIEW dbo.authRevers;

 this definition is contrary to standard as no order should be perfomed in view definition..

why do not (correctly) order outside the view, in the projection statement like

SELECT * FROM dbo.authRevers ORDER BY whatever;

you forced the TOP (n) as required by the ORDER BY.. then skip the whole semantic error and perform the sorting where it belongs..

regards

Answer 2

Hi Andrea,

Not too sure where you were going with your post.  I see your suggestion regarding ordering through the SQL selection code from my application but this isn't the problem.

The problem is that SQL Server doesn't carry out the ordering as it is supposed to and I don't know why....

Anyone else shed some light?

Ady

Answer 3

Moving to T-SQL forum so the experts can have a go at this one.

Mike

Answer 4

To get rows in particular order, you have to specify an ORDER BY clause in the outer-most query. For any other scope like view definition or derived table etc the ORDER BY only applies within that scope. See below links for more details:

 

http://msdn2.microsoft.com/en-us/library/ms187956.aspx

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

 

Also, if this worked in SQL Server 2000 it was only by chance due to the query plan.