Posted by David Portas on 01/24/06 22:57

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.

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.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]