Posted by Erland Sommarskog on 06/30/06 22:02

(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/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]