Posted by Erland Sommarskog on 06/30/06 13:01

(pltaylor3@gmail.com) writes:
> I have a query set up that returns the data that I would like, but I
> would only like the latest data for each vehicle number. The query I
> have set up is
>
> SELECT TOP 100 PERCENT dbo.vwEvents.EventName,
>....
> ORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,
> dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTime

I don't have the time to look into the problem as such, but I feel
obliged to point out that the above looks dubious.

TOP 100 PERCENT does not make any sense at all, that is just white noice,
so I suggest that you remove.

At this point, I am not surprised if you say that this in fact a view
definition, and you need the TOP 100 for the ORDER BY to be permitted.
Well, it is still white noise. In SQL 2000 a SELECT from the view is
very like to return rows in the order set up by the ORDER BY clause, but
that is just mere chance. In SQL 2005 this is far likely and more than one
has been bitten by this.

The only way to get an ordered result from a query is to include an ORDER
BY clause in the query itself. You cannot use views to encapsulate order.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]