How to add another field to a max query result without grouping

  • Thread starter Thread starter Barry A&P
  • Start date Start date
B

Barry A&P

I am trying to create a query to find the most recent locationID for each
item with a TrkID

but i can only seem to get it with two queries then join it back on lochist
date and TRKiD Is this the best way??
Here is the query to get the max date for each TRKID isnt there a clean way
to add in one more field "LocationID" ??

SELECT TRKID, Max(T_LocationHistory.LocHistDate) AS MaxOfLocHistDate
FROM T_LocationHistory
GROUP BY TRKID;

if i do i get the max date for each unique TrkID and LocationID combo

Thanks
 
Why does it seem you cant find the answer till you post a question??
Looked harder and got it with this

SELECT T_LocationHistory.TRKID, T_LocationHistory.LocationID,
T_LocationHistory.LocHistDate
FROM T_LocationHistory INNER JOIN
[SELECT T_LocationHistory.TRKID, Max(T_LocationHistory.LocHistDate) AS
MaxOfLocHistDate
FROM T_LocationHistory
GROUP BY T_LocationHistory.TRKID]. as Q
ON T_LocationHistory.Lochistdate = Q.MaxOfLocHistDate and
T_LocationHistory.trkid = Q.trkid;

Thank You John Spencer
 
Back
Top