If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to add another field to a max query result without grouping
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 |
#2
|
|||
|
|||
How to add another field to a max query result without grouping
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 "Barry A&P" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|