Posted by Tony Rogerson on 01/24/06 19:02

Hi,

Ordering was only a side effect of the statement in 2000 and 7.0; the ORDER
BY goes with the TOP and the output is order can only be gaurenteed by using
ORDER BY on the view itself as below...

select * from vwRouteReference order by ....

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Beowulf" <beowulf_is_not_here@hotmail.com> wrote in message
news:mssBf.23262$Ez3.18813@trnddc03...
>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.
>
> CREATE VIEW vwRouteReference
> AS
> SELECT TOP 100 PERCENT tblItem.ID,
> tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName, tblItem.Numb,
> tblQuestion.DescrPrimary AS Type
> FROM tblItem INNER JOIN tblQuestion
> ON (tblItem.ID = tblQuestion.Item_ID)
> WHERE (((tblItem.Category_ID)>0))
> UNION
> SELECT TOP 100 PERCENT tblItem.ID,
> tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,
> tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
> FROM tblItem
> WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
> (tblItem.Type) = 'Route'))
> ORDER BY tblItem.Numb

[Back to original message]