Max for each
-
I'm trying to select a bunch of rows but I only want the latest trips (trip_id) for each record. The following only gives me the latest trip for ALL records - only one row. What do I need to change? Thanks
SELECT S.record_count,
T.trip_id,
S.compart_no,
T.boat_id,
T.boat_type,
S.recordor_id,
S.record_type,
S.record_loc_type FROM DRY_records S, trip T
WHERE ( S.trip_id =* T.trip_id
AND S.boat_id =* T.boat_id
AND S.boat_type =* T.boat_type) and
T.trip_id = (select max(trip_id) from trip)
-
Something like this should get you close:
SELECT S.record_count,
max(T.trip_id),
S.compart_no,
T.boat_id,
T.boat_type,
S.recordor_id,
S.record_type,
S.record_loc_type
FROM DRY_records S, trip T
WHERE (S.trip_id = T.trip_id
AND S.boat_id = T.boat_id
AND S.boat_type = T.boat_type)
GROUP BY
S.record_count,
S.compart_no,
T.boat_id,
T.boat_type,
S.recordor_id,
S.record_type,
S.record_loc_type
Cheers
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I thnk that you just need to extend your derived table and join it :
SELECT s.Record_Count,
t.Trip_ID,
s.Compart_No,
t.Boat_ID,
t.Boat_Type,
s.Recordor_ID,
s.Record_Type,
s.Record_Loc_Type
FROM (SELECT Boat_ID,
Boat_Type,
MAX(Trip_ID) AS Trip_ID
FROM Trip
GROUP BY Boat_ID,
Boat_Type) mt
JOIN Trip t
ON t.Boat_ID = mt.Boat_ID
AND t.Boat_Type = mt.Boat_ID
AND t.Trip_ID = mt.Trip_ID
JOIN DRY_Records s
ON s.Boat_ID = mt.Boat_ID
AND s.Boat_Type = mt.Boat_ID
AND s.Trip_ID = mt.Trip_ID
The derived table (alias mt) lists the maximum Trip_ID for each Boat_ID and Boat_Type (Are both of these required as the primary key?) and this result is used to select records from the other two tables.
-
Sorry, my formatting seems to have disappeared!
SELECT s.Record_Count,
t.Trip_ID,
s.Compart_No,
t.Boat_ID,
t.Boat_Type,
s.Recordor_ID,
s.Record_Type,
s.Record_Loc_Type
FROM (SELECT Boat_ID,
Boat_Type,
MAX(Trip_ID) AS Trip_ID
FROM Trip
GROUP BY Boat_ID,
Boat_Type) mt
JOIN Trip t
ON t.Boat_ID = mt.Boat_ID
AND t.Boat_Type = mt.Boat_ID
AND t.Trip_ID = mt.Trip_ID
JOIN DRY_Records s
ON s.Boat_ID = mt.Boat_ID
AND s.Boat_Type = mt.Boat_ID
AND s.Trip_ID = mt.Trip_ID
-
I think you need the max(trip_id) for each boat_id and boat_type combination:
SELECT S.record_count,
T.trip_id,
S.compart_no,
T.boat_id,
T.boat_type,
S.recordor_id,
S.record_type,
S.record_loc_type FROM DRY_records S, trip T
WHERE ( S.trip_id =* T.trip_id
AND S.boat_id =* T.boat_id
AND S.boat_type =* T.boat_type)
AND T.trip_id = (
SELECT max(M.trip_id) FROM trip M
WHERE T.boat_id = M.boat_id
AND T.boat_type = M.boat_type
)
Here's an alternative:
SELECT S.record_count,
M.trip_id_max,
S.compart_no,
M.boat_id,
M.boat_type,
S.recordor_id,
S.record_type,
S.record_loc_type
FROM DRY_records S,
(SELECT T.boat_id, T.boat_type, MAX(T.trip_id) AS trip_id_max
FROM trip T
GROUP BY T.boat_id, T.boat_type
) AS M
WHERE ( S.trip_id =* M.trip_id
AND S.boat_id =* M.boat_id
AND S.boat_type =* M.boat_type)
Good luck
Regards,Bob Monahon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply