-1

I am trying to return the Task that has a note linked to it with the most recent timestamp. Although this query works, it's not returning the most recent timestamp consistently. Is there another method to doing this? 

SELECT DISTINCT  tasks.TaskID, tasks.desc, tasks.startdate, tasks.active, notes.note,          max(notes.NoteDate) 
FROM tasks INNER JOIN notes ON tasks.TaskID=notes.NoteID 
WHERE notes.NoteDate >= curdate() 
GROUP BY tasks.TaskID
T0w3ntuM
  • 332
  • 2
  • 3
  • 12
  • possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) – Barmar Apr 30 '13 at 16:37

1 Answers1

0

Try something along the lines of:

SELECT tasks.TaskID, tasks.desc, tasks.startdate, tasks.active, notes.note,          notes.NoteDate
FROM tasks INNER JOIN notes ON tasks.TaskID=notes.NoteID 
order by NoteDate Desc 
limit 1

Update, if you want more than one row (for example where notedate in last day):

SELECT tasks.TaskID, tasks.desc, tasks.startdate, tasks.active, notes.note,          notes.NoteDate
FROM tasks INNER JOIN notes ON tasks.TaskID=notes.NoteID 
where notes.NoteDate > date_sub(current_date(),interval 1 day)
order by NoteDate Desc
Ian Kenney
  • 6,376
  • 1
  • 25
  • 44
  • The problem with that is I'm trying to return multiple records, not just one. I need a list of ALL the tasks that were most recently updated. – T0w3ntuM Apr 30 '13 at 16:41
  • what does most recently mean ? – Ian Kenney Apr 30 '13 at 16:41
  • Updated today, if that helps. So I want a list of tasks that have had notes added today. Also, the NoteID is a timestamp field and not just a date. – T0w3ntuM Apr 30 '13 at 16:55