Returning the newest rows

[pltaylor3@gmail.com]

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.Ev entName,
dbo.luSessionAl l.SessionName, dbo.luOuting.Ou tingNumber,
dbo.luVehicle.V ehicleName, dbo.luOuting.Ou tingID,
dbo.tblOutings. OutingStartTime ,dbo.tblSession s.SessionDate,d bo.tblSessions. SessionStartTim e
FROM dbo.vwSessions INNER JOIN dbo.vwEvents ON
dbo.vwSessions. Event = dbo.vwEvents.Ev entID
INNER JOIN
dbo.luSessionAl l ON dbo.vwEvents.Ev entID =
dbo.luSessionAl l.Event INNER JOIN
dbo.luOuting ON dbo.luSessionAl l.SessionID =
dbo.luOuting.Se ssionID INNER JOIN
dbo.luVehicle ON dbo.luSessionAl l.Vehicle =
dbo.luVehicle.V ehicleID INNER JOIN
dbo.tblOutings ON dbo.luOuting.Ou tingID =
dbo.tblOutings. OutingID INNER JOIN
dbo.tblSessions ON dbo.tblOutings.[Session] =
dbo.tblSessions .SessionID
GROUP BY dbo.vwEvents.Ev entName, dbo.luSessionAl l.SessionName,
dbo.luOuting.Ou tingNumber, dbo.luVehicle.V ehicleName,
dbo.luOuting.Ou tingID, dbo.tblOutings. OutingStartTime ,
dbo.tblSessions .SessionStartTi me, dbo.tblSessions .SessionDate
ORDER BY dbo.luVehicle.V ehicleName, dbo.tblSessions .SessionDate,
dbo.tblSessions .SessionStartTi me, 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

[Erland Sommarskog]

Re: Returning the newest rows

(pltaylor3@gmai l.com) writes:[color=blue]
> 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.Ev entName,
>....
> ORDER BY dbo.luVehicle.V ehicleName, dbo.tblSessions .SessionDate,
> dbo.tblSessions .SessionStartTi me, dbo.tblOutings. OutingStartTime[/color]

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@sommarsk og.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

[Erland Sommarskog]

Re: Returning the newest rows

(pltaylor3@gmai l.com) writes:[color=blue]
> 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[/color]

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.OutingStartT ime,
S.SessionDate,S .SessionStartTi me
FROM dbo.vwSessions Ss
JOIN dbo.vwEvents E ON Ss.Event = E.EventID
JOIN dbo.luSessionAl l 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.tblSesseion s S1 ON
Os.[Session] = S.SessionID
ORDER BY S1.SessionDate DESC,
S1.SessionSessi onStartTime DESC,
Os1.OutingStart Time DESC)
JOIN dbo.tblSessions S ON Os.[Session] = S.SessionID
ORDER BY V.VehicleName, S.SessionDate, S.SessionStartT ime,
Os.OutingStartT ime

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@sommarsk og.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