MySQL - Join tables, retrieve only Max ID

Question

I've seen solutions for something similar on other posts, but I've been having an issue applying it to my specific problem.

Here is my initial join:

SELECT service_note_task, comment_id, comment FROM service_note_task LEFT JOIN service_note_task_comments ON service_note_task.service_note_task_id = service_note_task_comments.service_note_task_id;  

Which results in:

+-----------------------------+------------+--------------+
| service_note_task           | comment_id | comment      |
+-----------------------------+------------+--------------+
| This is service note task 3 |         25 | Comment      |
| This is service note task 3 |         26 | Comment Blah |
| This is service note task 3 |         36 | aaa          |
| This is service note task 2 |         13 | Awesome comm |
| This is service note task 1 |         12 | Cool Comm    |
+-----------------------------+------------+--------------+

But for each service_note_task, I really only need one row representing the comment with the highest comment_id, like this:

+-----------------------------+------------+--------------+
| service_note_task           | comment_id | comment      |
+-----------------------------+------------+--------------+
| This is service note task 3 |         36 | aaa          |
| This is service note task 2 |         13 | Awesome comm |
| This is service note task 1 |         12 | Cool Comm    |
+-----------------------------+------------+--------------+

I figure I could use MAX in a sub-select statement to narrow down the results as I want them. How can I incorporate that into my statement to get these results?

Answer 1

For reference, this is known as "groupwise-maximum"

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Answer 2

try:

SELECT service_note_task, comment_id, comment 
FROM service_note_task SNT1 
LEFT JOIN service_note_task_comments ON service_note_task.service_note_task_id = service_note_task_comments.service_note_task_id
WHERE comment_id = (SELECT MAX(comment_id) FROM  service_note_task SNT2 WHERE SNT1.service_note_task = SNT2.service_note_task);