CODEWITHSUNDEEP
mysql
MevlütÖzdemir
MevlütÖzdemir

Reputation: 3460

sql query to get max date of group

I want from each WishContent the newest date based on the wishContent.wishId So if I have wishContents like this: 

ID   DATE
#1 : 09-03-2016
#1 : 08-03-2016
#1 : 03-04-2016
#2 : 09-02-2016
#2 : 04-01-2016

Then I only want: 

#1 09-03-2016
#2 09-02-2016

SELECT
      wish.Status,
      wish.Id,
      wish.User,
      wish.CompletionDate,
      wishContent.Content,
      wishContent.Title,
      wishContent.Country,
      wishContent.City,
      wishContent.IsAccepted,
      wishContent.moderator_Username,
      MAX(wishContent.Date) AS max_date
      FROM `wish` JOIN wishContent on wish.Id = wishContent.wish_Id
      GROUP BY wish.Id where wish.Date
      ORDER BY max_date DESC

Can anyone help me ?

Upvotes: 0

Views: 48

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

I think you need an additional join to get the required result:

SELECT
      w.Status,
      w.Id,
      w.User,
      w.CompletionDate,
      wc.Content,
      wc.Title,
      wc.Country,
      wc.City,
      wc.IsAccepted,
      wc.moderator_Username,
      wcMax.max_date
      FROM wish AS w
      JOIN (SELECT wish_Id, MAX(wishContent.Date) AS max_date 
            FROM wishContent
            GROUP BY wish_Id
      ) AS wcMax ON w.Id = wcMax.wish_Id
      JOIN wishContent AS wc on wcMax.wish_Id = wc.wish_Id AND wc.Date = wcMax.max_date
      WHERE wish.Date
      ORDER BY max_date DESC

Upvotes: 3

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

SELECT * FROM (
SELECT
      wish.Status,
      wish.Id,
      wish.User,
      wish.CompletionDate,
      wishContent.Content,
      wishContent.Title,
      wishContent.Country,
      wishContent.City,
      wishContent.IsAccepted,
      wishContent.moderator_Username,
      MAX(STR_TO_DATE(wishContent.Date,'%d-%m-%Y')) AS max_date
      FROM `wish` JOIN wishContent on wish.Id = wishContent.wish_Id
      GROUP BY wish.Id
     ) AS t
      ORDER BY t.max_date DESC;

I Assume your date format is dd-mm-yyyy.

Upvotes: 1

Related Questions