CODEWITHSUNDEEP
mysqlsql
user813813
user813813

Reputation: 355

How to select last record by date in a joined table

I have 2 tables, Car_Table and History_Table. I want to be able to select the last history item for all cars when their status is set to broken.

The tables are as follows

Car_Table
Car_ID  Driver_Name Car_Status
1       Alan        Broken
2       Dave        Broken

and

History_Table
id  Date        Notes       Car_Id
1   01-01-2017  Change oil      1
2   02-01-2017  Check Brakes    1
3   02-01-2017  Service         2
3   03-01-2017  Cleaning        2

When I do

select Car_Table.Driver_Name,History_Table.Notes from Car_Table 
inner join History_Table on Car_Table.Car_ID = History_Table.CarID 
where Car_Table.Car_Status = 'Broken'

I get all of the history records returned. Is there a way to get the last history item for each car that has a status of "broken" ?

Upvotes: 0

Views: 83

Answers (3)

Pramod
Pramod

Reputation: 61

You can use a subquery to get last record. Ideally there should be an index on the 'date' field if the records are large in number.

select Car_Table.Driver_Name,History_Table.Notes 
  from Car_Table 
  inner join History_Table on Car_Table.Car_ID = History_Table.CarID 
    and History_Table.id=(
      select History_Table2.id 
        from History_Table2 
        Where History_Table2.CarID=History_Table.CarID
        Order by date desc 
        Limit 1
    )
  where Car_Table.Car_Status = 'Broken'

Upvotes: 1

ad4s
ad4s

Reputation: 304

The most efficient way to get such reports would be to create an extra column last_history_id in the Car_Table. Each time a record is inserted to the History_Table a trigger would update the last_history_id column in Car_Table. It would decrease read latency for selecting the desired data-sets, but in some way may be a trick if many updates would be performed at a time.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can find row with max date for each car using the left join technique and then join it with the Car_table:

select Car_Table.Driver_Name,
    History_Table.Notes
from Car_Table
inner join (
    select h1.*
    from History_Table h1
    left join History_Table h2 on h1.Car_ID = h2.Car_ID
        and h1.date < h2.date
    where h2.Car_ID is null
    ) History_Table on Car_Table.Car_ID = History_Table.CarID
where Car_Table.Car_Status = 'Broken';

See official MySQL site for alternate solutions (using aggregation in correlated and uncorrelated subquery, which are likely to be slower than the solution above).

Upvotes: 4

Related Questions