2

I have two tables jobs, notes.

I want to output a list of jobs that are of status='lost' along with the most recent note for that job (based on the date the note was created).

Here's my query:

select jobs.id, jobs.name, jobs.status
inner join notes on jobs.id=notes.jobId
where jobs.status='lost'
group by jobs.id
order by notes.createDate DESC

I would have thought that the output would show the most recent note for a given job. But it shows the first note for that job. I have changed sort from DESC to ASC just to see what happens...and the output is the same.

Then I tried to nest a select from notes inside the main select..and it hung.

This should be easy and I am sure it is..what am I missing ?

lilbiscuit
  • 2,109
  • 6
  • 32
  • 53
  • 1
    Actually, it returns arbitrary (indeterminate) results. You're missing the manual, which has a whole chapter on this topic. – Strawberry Jun 28 '16 at 16:11
  • 1
    The manual page: http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – axiac Jun 28 '16 at 16:30
  • ... and an answer to a similar question that can help you: http://stackoverflow.com/a/28090544/4265352 – axiac Jun 28 '16 at 16:31

2 Answers2

0

There is many options to solve this, but you may use a sub query.

select jobs.id, jobs.name, jobs.status
(select noteField from notes on jobs.id=notes.jobId order by createDate desc limit 1) note
where jobs.status='lost'
Sven Tore
  • 967
  • 6
  • 29
0

When I'm in a similar boat, I've resorted to using a subquery on the join: 

select jobs.id, jobs.name, jobs.status
from jobs
inner join notes on jobs.id = notes.jobId
   and notes.createDate = (select max(notes.createDate)
                          from notes
                          where jobs.id = notes.createDate
                          group by notes.jobId)
where jobs.status='lost'
group by jobs.id
order by notes.createDate DESC
ltz
  • 1