CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Dmitri Snytkine
Dmitri Snytkine

Reputation: 1126

How to select from 2 tables and get highest value from one table?

I am having a hard time coming up with SQL statement for this situation: Imagine there are multiple vehicles on the road at any given time, each of these trips is in the trips table. Each vehicle regularly sends out the updates of its current location (lat, lon)

There are 2 tables: current_position

id | trip_id | update_time | lat | lon

trips

id | status

current_position has multiple updates for each trip

I need to get results, having trip_id, lat, lon

for each trip that has status = 'DRIVING' and must have the latest values from current_position

Latest value I mean highest value of update_time. When I tried the GROUP BY I got distinct results but the results are were not always the latest position. I can't seem to combine the order by update_time DESC with GROUP BY trip_id

Upvotes: 1

Views: 412

Answers (4)

Bohemian
Bohemian

Reputation: 425033

With mysql, group by without an aggregate column give you the first row for each distinct value, so use group by after the order by.

It's a simple as this:

select trip_id, lat, lon
from (select * from current_position
      join trips on trips.id = current_position.id
      where status = 'DRIVING'
      order by trip_id, update_time desc) x
group by trip_id

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

Another way:

SELECT
    cp.*
FROM
        ( SELECT id
          FROM trips 
          WHERE status = 'DRIVING'
        ) AS t
    JOIN
        current_position AS cp 
      ON
        cp.id =
        ( SELECT cpm.id
          FROM current_position AS cpm
          WHERE cpm.trip_id = t.id
          ORDER BY cpm.update_time DESC
          LIMIT 1
        )

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Start with a prequery by trip ID and max time, then re-join that back based on most recent time. However, if the current positions table's ID column is auto-increment column, and the update_time will always be greater than the last one reporting in, I would instead change the max() of the time to the max of the ID of the current position table as that would be best on group by and index when joining after the "PreQuery"

I would also have an index on your trips table by the status to shorten your query results. As your table builds, if you start to have 10,000 trips, and only 15 are currently being driven for delivery (or whatever), you'll only be getting those active 15 and not span the entire trips table.

select
      cp2.*
  from
      ( select t.id, 
               max( cp.update_time ) as MostRecent
           from
              trips t
                 join current_position cp
                   on t.id = cp.trip_id
           where
              t.status = 'Driving'
           group by 
              t.id ) PreQuery
      join current_position cp2
         on PreQuery.id = cp2.trip_id
         and PreQuery.MostRecent = cp2.update_time

Upvotes: 1

nycdan
nycdan

Reputation: 2839

it sounds like you want to select the current_position row with the largest update_time value for a given trip_id. You can do this with a subquery using MAX.

Select * from current_position
Where trip_id = @trip_id 
and update_time = (Select Max(update_time) from current_position where trip_id = @trip_id)

Edit...after posting, i realize you need a join to get all rows where the corresponding trip status = 'driving'. I think this will do it (although I am more Sql Server so there might be some slight difference with MySQL I'm unaware of).

Select * from current_position cp
join trips t on cp.trip_id = t.id
where t.status = 'Driving' 
and cp.update_time = (select Max(update_time) from current_position where trip_id = t.id)

Upvotes: 1

Related Questions