CODEWITHSUNDEEP
mysqlgroup-by
anon
anon

Reputation:

MYSQL GROUP BY not showing correct date

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

Answers (1)

Darshan Mehta
Darshan Mehta

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

Related Questions