Friday, September 22, 2006 9:45 AM
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
ORDER BY Name
Friday, September 22, 2006 5:02 PM
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..
Monday, September 25, 2006 12:11 PM
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?
Monday, September 25, 2006 10:12 PM
Moving to T-SQL forum so the experts can have a go at this one.
Monday, September 25, 2006 11:40 PMTo 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:Also, if this worked in SQL Server 2000 it was only by chance due to the query plan.