CODEWITHSUNDEEP
sqlmysqldatabase
brux
brux

Reputation: 3230

sql select,get most recent entry for each car

My database (MySql) has 2 tables

Table 1 - contains a record for each car

cars= id(int), reg(int), type(int)

Table 2 - contains jobs for each car

jobs = id(int),date(date)

I need a SELECT command which will get me the most recent job for all id's

Thanks in advance.

Upvotes: 2

Views: 1023

Answers (4)

jon_darkstar
jon_darkstar

Reputation: 16778

You don't have a shared field, you cant do this. You need to store the associated car_id with each job, or have a jobs_to_cars table.

Then you'd use:

SELECT C.*, J.id, MAX(J.date)
FROM cars C 
INNER JOIN jobs J on J.car_id = C.id
GROUP BY C.id

If job's id IS the same as car's id then change j.car_id to j.id, but then you'd be lacking primary key on Jobs, which i suggest youd start making.

Upvotes: 4

Mario
Mario

Reputation: 196

You could use something like this

SELECT c.*, MAX(j.date) date
FROM cars c 
   LEFT JOIN jobs j ON j.id = c.id
GROUP BY c.id
ORDER BY c.id

because you don't have other fields in [jobs]. If a car don't have any jobs, date will be NULL.

Upvotes: 2

zerkms
zerkms

Reputation: 255025

    SELECT *
      FROM cars c
INNER JOIN (SELECT MAX(id) AS j_id
              FROM jobs
          GROUP BY car_id) x ON x.j_id = c.id
  1. You forgot the jobs.car_id field, that specifies which car job belongs to
  2. More elegant way will be to create the field car.last_job_id and maintain it with trigger on jobs table.

Since your question is not clear, this query also can be an answer:

    SELECT j.*
      FROM jobs j
INNER JOIN (SELECT MAX(id) id
              FROM jobs
          GROUP BY car_id) x ON x.id = j.id

Upvotes: 1

Woppi
Woppi

Reputation: 5451

You can try 

ORDER BY date DESC

Upvotes: 0

Related Questions