Posted by Beowulf on 01/25/06 16:49

David Portas wrote:
> Beowulf wrote:
>> I have the view below and if I use vwRouteReference as the rowsource
>> for a combo box in an MS Access form or run "SELECT * FROM
>> vwRouteReference" in SQL Query Analyzer, the rows don't come through
>> sorted by Numb.
>>
>> Everything I've read on the web suggests that including the TOP
>> directive should enable ORDERY BY in views. Does someone have an idea
>> why the sorting is not working correctly for this particular view? thanks.
>>
>
> You are reading the wrong stuff! :-) The order is determined here:
>
> SELECT * FROM vwRouteReference
>
> ... and you didn't specify any order, so you get what you asked for -
> the ordering is arbitrary. What you put in the view doesn't necessarily
> determine the order that is returned by your SELECT statement. Since
> the ORDER BY in your view doesn't fix the order to be returned, SQL
> Server's optimizer is perfectly within its rights to ignore it.

So, it's just a coincidence (or perhaps luck) that in other views (see
below) the ORDER BY is respected and the rows returned by the view are
sorted in the specified order?

e.g., this works as a newbie such as myself would expect:
CREATE VIEW qryAnnotated_Item
AS
SELECT TOP 100 PERCENT getdate() AS FormVersion,
tblCategory.Mnemonic, tblCategory.Numb AS Category_Numb,
tblCategory.Descr,
tblItem.ID, tblItem.Numb as Item_Numb,
tblItem.Type, tblItem.Notes,
CASE
WHEN (tblItem.RevisionDate > tblItem.CreationDate)
THEN tblItem.RevisionDate
ELSE tblItem.CreationDate
END AS ChangeDate
FROM tblCategory INNER JOIN tblItem
ON (tblCategory.ID=tblItem.Category_ID)
WHERE ((tblCategory.ID>0)
ORDER BY tblCategory.Numb, tblItem.Numb

> The solution is:
>
> SELECT *
> FROM vwRouteReference
> ORDER BY numb ;
>
> Don't use "SELECT *" in real code. It's sloppy, inefficient and hurts
> when it comes to reliability and maintenance.

Yeah, it's just what I was running in the QA window to test out sorting.
Thanks for the feedback.

[Back to original message]