Reputation:
I have run into a small problem with the GROUP BY
function in MYSQL v. 5.7.15-log.
SELECT ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter, co.CommentDate
FROM forum_category AS ca
JOIN forum_comment AS co
ON co.CategoryID = ca.CategoryID
ORDER BY ca.CategoryID ASC, co.CommentDate DESC
resulting in
CategoryID CategoryName CategoryDescription Commenter CommentDate
1 Admin test1 Pino "2017-04-16 15:47:41"
1 Admin test1 Pino "2017-04-16 15:36:40"
1 Admin test1 Pino "2017-04-16 15:25:30"
2 test bla Pino "2017-04-16 17:17:56"
2 test bla Pino "2017-04-16 17:17:21"
2 test bla Pino "2017-04-16 15:56:16"
Where what i want is
CategoryID CategoryName CategoryDescription Commenter CommentDate
1 Admin test1 Pino "2017-04-16 15:47:41"
2 test bla Pino "2017-04-16 17:17:56"
I have tried a few different thing, but no luck so far.
I did read in here to try
SELECT a.CategoryID, CategoryName, CategoryDescription, Commenter, CommentDate FROM (
SELECT ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter, co.CommentDate
FROM forum_category AS ca
JOIN forum_comment AS co
ON co.CategoryID = ca.CategoryID
ORDER BY ca.CategoryID ASC, co.CommentDate DESC
) AS a
ORDER BY a.CategoryID
but that only resulted in the wrong date as well
CategoryID CategoryName CategoryDescription Commenter CommentDate
1 Admin test1 Pino "2017-04-16 15:25:30"
2 test bla Pino "2017-04-16 15:56:16"
Thanks in advance to the MYSQL Ninjas out there
Upvotes: 0
Views: 17
Reputation: 30849
Try this:
SELECT ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter, MAX(co.CommentDate) as max_date
FROM forum_category AS ca
JOIN forum_comment AS co ON co.CategoryID = ca.CategoryID
GROUP BY ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter
ORDER BY ca.CategoryID ASC, max_date DESC;
Upvotes: 1