Using Max And Group By Twice

I read some articles on this topic including: Use of GROUP BY twice in MySQL and know this should be the same logic I need but I just don’t know how to apply it. Please note that

Solution 1:

You can group by on multiple columns by separating them with a comma. Change:

groupby titleid THEN editionID

to:

groupby titleid, editionID

Solution 2:

What you need are the window functions, in particular, the one for max(). The following gets you the max published date for each edition:

Select  tt.titleid, ed.editionID, pb.datepublished,
        max(datepublished) over (partitionby e.editionId) as EditionPublished
FROM    title tt  leftjoin
        edition ed
        on tt.titleid=ed.titleid leftjoin
        published pb
        on pb.stockno=ed.stockno;