John Beasley John Beasley - 22 days ago 7
PHP Question

MYSQL selecting all records with the most recent date

Our world is Coder than theirs. Code World, people.

Good Morning, StackOverflow.

Simple request today...

I have a database that stores a history of transactions, mostly comment updates. Therefore, I accept multiple records with the same ID.

What I need to do is pull in all of the records and display the most recent of each record.

Here is an example of 2 records with the same UID. The highlighted record is the most recent of the 2.

enter image description here

Here is one of the queries I attempted:

SELECT
uid
,voyage
,MAX(comments)
,MAX(edituser)
,MAX(editdate)
FROM
table
GROUP BY
uid
ORDER BY
uid


Here is the same UID that is returned:

enter image description here

If you'll notice, it is the most recent user and editdate, but it's not the most recent comment, which should be 'THIS IS A TEST COMMENT'.

I have tried several queries, but this is the closest I've gotten to returning the most recent of every record.

I tried this link: SQL Select record with the most recent date

But I don't think I need to use any JOINS in this case, being that everything is in the same table. But then again, my strength is not in MYSQL.

I'm hoping someone can help me by providing the correct (and better) query to make this work.

Please help, and thank you in advance.

Answer

If that's what your desired output be then you should use MIN() instead like MIN(comments)