Используя Макс и сгруппировать в два раза

Question

Я прочитал несколько статей на эту тему, в том числе: Использование GROUP BY дважды в MySQL и знаю, что это должна быть та же логика, что и мне, но я просто не знаю, как ее применять.

Обратите внимание, что я знаю, что схема ужасна (я до сих пор не понимаю, почему редакция и опубликованные таблицы, например, разделены...), но у меня нет никаких полномочий изменить ее.

(У меня есть некоторая конфиденциальная информация, с которой я работаю, поэтому я смоделирую свою проблему на примере с использованием фильмов.)

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

Я хочу вернуть строки, где каждая строка является изданием одного из названий. Для каждого издания заголовка я хочу вернуть самую последнюю опубликованную дату, независимо от СМИ.

Например:

Отличный ход #1, Обычная версия, Latest_published_date

Отличный фильм № 1, Расширенная версия, Latest_published_date

Отличный ход #2, обычная версия, Latest_published_date

Отличный фильм № 2, расширенная версия, Latest_published_date

Я просто потерян в море логики...

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?!?!?!?

Answer 1

Вы можете группировать по нескольким столбцам, разделяя их запятой. Изменить:

group by titleid THEN editionID

чтобы:

group by titleid, editionID

Answer 2

Что вам нужно, так это window функции, в частности, для max(), Ниже приведена максимальная дата публикации для каждого издания:

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;