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,
dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,
dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,
dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTime
FROM dbo.vwSessions INNER JOIN dbo.vwEvents ON
dbo.vwSessions.Event = dbo.vwEvents.EventID
INNER JOIN
dbo.luSessionAll ON dbo.vwEvents.EventID =
dbo.luSessionAll.Event INNER JOIN
dbo.luOuting ON dbo.luSessionAll.SessionID =
dbo.luOuting.SessionID INNER JOIN
dbo.luVehicle ON dbo.luSessionAll.Vehicle =
dbo.luVehicle.VehicleID INNER JOIN
dbo.tblOutings ON dbo.luOuting.OutingID =
dbo.tblOutings.OutingID INNER JOIN
dbo.tblSessions ON dbo.tblOutings.[Session] =
dbo.tblSessions.SessionID
GROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,
dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,
dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,
dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDate
ORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,
dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTime

this returns all the outings. I would like the outing that has, in
order of importance, the latest session date, latest session time and
latest outing start time. Outing start time can sometimes be <<Null>>
but the other two always have values. How would I go about doing this?
thanks in advance for any help

(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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

(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
>...
> this returns all the outings. I would like the outing that has, in
> order of importance, the latest session date, latest session time and
> latest outing start time. Outing start time can sometimes be <<Null>>
> but the other two always have values. How would I go about doing this?
> thanks in advance for any help

Without know what is what in the tables, I will have to guess a bit.
Maybe this is what you are looking for. And if it's not, maybe it's
enough to get you going:

SELECT E.EventName, SA.SessionName, O.OutingNumber,
V.VehicleName, O.OutingID, Os.OutingStartTime,
S.SessionDate,S.SessionStartTime
FROM dbo.vwSessions Ss
JOIN dbo.vwEvents E ON Ss.Event = E.EventID
JOIN dbo.luSessionAll SA ON E.EventID = SA.Event
JOIN dbo.luOuting O ON SA.SessionID = O.SessionID
JOIN dbo.luVehicle V ON SA.Vehicle = V.VehicleID
JOIN dbo.tblOutings Os ON O.OutingID =
(SELECT TOP 1 Os.OutingID
FROM dbo.tblOutings Os1
JOIN dbo.tblSesseions S1 ON
Os.[Session] = S.SessionID
ORDER BY S1.SessionDate DESC,
S1.SessionSessionStartTime DESC,
Os1.OutingStartTime DESC)
JOIN dbo.tblSessions S ON Os.[Session] = S.SessionID
ORDER BY V.VehicleName, S.SessionDate, S.SessionStartTime,
Os.OutingStartTime

I replaced the table/view names with alias to make the queries easier
to read.

I also did away with the GROUP BY that did not seem to serve any
purporse. Maybe it's a DISTINCT you need. Then again, if you need a
DISTINCT this is an indication that the query is lacking a condition
somewhere.



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx