Using max and group by twice

Question

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 I know the schema is terrible (I still don’t understand why the edition and published tables are separate for example….) but I don’t have any power to change it.

(I have some sensitive information that I am working with so I will model my problem using an example using movies instead.)

Tables:

Title
TitleID     MovieTitle
1           Great Movie #1
2           Great Movie #2

Edition
StockNo editionID   EditionText         Media       TitleID
1           1       Regular Edition     DVD         1
2           1       Regular Edition     Blue-ray    1
3           2       Extended Version    DVD         1
4           2       Extended Version    Blue-ray    1
5           1       Regular Edition     DVD         2
6           1       Regular Edition     Blue-ray    2
7           2       Extended Version    DVD         2
8           2       Extended Version    Blue-ray    2

Published
StockNo DatePublished
1       1999.01.01
2       2003.01.01
3       2000.01.01
4       1999.01.01
5       1997.01.01
6       1998.01.01
7       2012.01.01
8       2009.01.01

I want to return rows with each row being an edition of one of the titles. For each edition of the title I want to return the latest published date, regardless of the media.

E.g.:

Great move #1, Regular version, Latest_published_date

Great movie #1, Extended version, Latest_published_date

Great move #2, regular version, Latest_published_date

Great movie #2, extended version, Latest_published_date

I’m just lost in a sea of logic….

WITH Datespublished AS
(   Select  tt.titleid
            ,ed.editionID
            ,pb.datepublished 
    FROM    title tt
            left join edition ed on tt.titleid=ed.titleid
            left join published pb on pb.stockno=ed.stockno
)


select titleid, editionID, max (datepublished) as maxdate from Datespublished group by titleid THEN editionID?!?!?!?

score 1 · Accepted Answer · answered Aug 05 '13 at 14:11

1

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

group by titleid THEN editionID

to:

group by titleid, editionID

answered Aug 05 '13 at 14:11

Andomar

232,371
49
380
404

Perfect! You have saved me hours of agony and taught me something new! I can't thank you enough! (It's funny how sometimes when we think a problem requires a complex solution, it actually just requires a relatively simple one. But we have to be looking for it to see it!!!) – Sarah Aug 05 '13 at 14:30

score 0 · Answer 2 · answered Aug 05 '13 at 14:07

0

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 (partition by e.editionId) as EditionPublished
FROM    title tt  left join
        edition ed
        on tt.titleid=ed.titleid left join
        published pb
        on pb.stockno=ed.stockno;

answered Aug 05 '13 at 14:07

Gordon Linoff

1,242,037
58
646
786

That'll still list all editions, not just the latest per `(titleid, editionid)`. And I don't think MySQL supports row functions. – Andomar Aug 05 '13 at 14:14

Using max and group by twice

2 Answers2

Linked