Select the most recent note/comment from an application

Question

I have 2 relevant tables here, application and application_note.

I want to find the latest note (user and text/note) for each application.

application_note looks like

+----------------+-------------+------+-----+-------------------+-----------------------------+
| Field          | Type        | Null | Key | Default           | Extra                       |
+----------------+-------------+------+-----+-------------------+-----------------------------+
| id             | int(11)     | NO   | PRI | NULL              | auto_increment              |
| create_time    | timestamp   | NO   |     | CURRENT_TIMESTAMP |                             |
| update_time    | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| application_id | int(11)     | YES  | MUL | NULL              |                             |
| note           | text        | YES  |     | NULL              |                             |
| user           | varchar(50) | YES  |     | NULL              |                             |
+----------------+-------------+------+-----+-------------------+-----------------------------+

I've been trying a bunch of different queries. The closest thing I have is

SELECT user, note, application_id, MAX(create_time)
FROM application_note
GROUP BY application_id

which looks like the max(create_time) is what I expect, but the other values are off. I've been trying at this for awhile and have been getting no where.

edit: I plan to eventually add this a view or add this to a larger query, if that changes anything.

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jun 28 '17 at 20:32

score 2 · Accepted Answer · answered Jun 28 '17 at 20:35

You have to join the table back on itself:

SELECT b.application_id, b.ct, a.note, a.user
FROM (SELECT application_id, MAX(create_time) AS ct
FROM application_note
GROUP BY application_id) b
INNER JOIN application_note a ON a.application_id=b.application_id
   AND a.create_time=b.ct

This will return the record with the latest creation time for a given application id. Duplicates might occur when you have multiple records with the same creation_time for a given application_id

Brilliant, that seems to do the trick – Shatnerz Jun 28 '17 at 20:40 — Shatnerz, Jun 28 '17 at 20:40

Select the most recent note/comment from an application

1 Answers1