2

=================
ID - Date - Note
3 - 1/1/2014 - happy
3 - 2/1/2014 - mad
3 - 3/1/2014 - sad
4 - 1/1/2014 - mad
4 - 2/1/2014 - happy
=================

Would like to return the latest date per ID as well as associated Note. Results would look like this:

=================
ID - Date - Note
3 - 3/1/2014 - sad
4 - 2/1/2014 - happy
=================

I can group by ID and then select the max(Date). However, I can't get the associated Note as no aggregate function is applicable. I just want: "the note associated with the Id/date I selected via the max function."

I don't know the official or proper or efficient way to do it. It feels like i'm hacking it by rejoining the aggregate query back into the original data set. Any help would be greatly appreciated as i constantly run into this issue.

Barmar
  • 741,623
  • 53
  • 500
  • 612
user3646932
  • 486
  • 4
  • 12

1 Answers1

4

One easy way is to wrap the max query in a subselect:

select
  m.id, m.datecolumn, m.note
from
  (select max(datecolumn) datecolumn, id
   from mytable
   group by id) sub
inner join mytable m on m.id = sub.id and m.datecolumn= sub.datecolumn
crthompson
  • 15,653
  • 6
  • 58
  • 80
  • 1
    You need to give an alias to `max(datecolumn)` in the subquery, don't you? – Barmar Aug 06 '14 at 17:00
  • Is this really the best way? Seemed overly complicated to me, I figured there must be an easier way. Oh well, Thanks for the reply! – user3646932 Aug 06 '14 at 17:03
  • @user3646932 Some databases have easier ways. That's why I asked you to specify the DBMS you're using. Maybe someone who knows Access will post something simpler. – Barmar Aug 06 '14 at 17:05
  • Barmar, thanks, edited. @user3646932, given its an AccessDB, there are limits to the sql you can perform. I can help however with more direction on what you to do. – crthompson Aug 06 '14 at 19:47
  • Thanks paqpgomez. Your solution makes sense to me. I was hoping there was a magical "get associated field" function that I could throw in w/o nesting another select query, but alas, it seems not. I really appreciate you taking the time to help. Unless you can think of anything else you want to say about it, the above solution works fine. Thanks. – user3646932 Aug 07 '14 at 15:28
  • Lots of ways to skin this cat, but I find the most straightforward to be the best. Easy to understand and maintain and with the right indexes, it should be snappy. Good luck! – crthompson Aug 07 '14 at 15:33