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

SELECT service_note_task, comment_id, comment
FROM service_note_task s LEFT JOIN service_note_task_comments sc
  ON s.service_note_task_id = sc.service_note_task_id;
WHERE EXISTS (
              SELECT 1
              FROM service_note_task_comments s2
              WHERE s.service_note_task_id = s2.service_note_task_id
              HAVING MAX(s2.comment_id) = sc.comment_id
              )

Answer 2

since you haven't mention the RDBMS you are using, this query below mostly works on many RDBMS (not all)

SELECT  a.*, b.*   -- select only the columns you want. 
FROM    service_note_task a
        INNER JOIN service_note_task_comments b
            ON a.service_note_task_id = b.service_note_task_id
        INNER JOIN 
        (
            SELECT  service_note_task_id, MAX(commentID) max_ID
            FROM    service_note_task_comments
            GROUP   BY service_note_task_id
        ) c ON  b.service_note_task_id = c.service_note_task_id AND
                b.commentID = c.max_ID

if your RDBMS supports Analytical Functions, you can use this below,

SELECT  a.service_note_task, b.comment_id, b.comment 
FROM    service_note_task a
        INNER JOIN 
        (
            SELECT  service_note_task_id, comment_id, comment,
                    ROW_NUMBER() OVER (PARTITION BY service_note_task_id
                                        ORDER BY comment_id DESC) rn
            FROM    service_note_task_comments
            GROUP   BY
        ) c ON  a.service_note_task_id = b.service_note_task_id AND
                b.rn = 1

Answer 3

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

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

Answer 4

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);