SQL join left get MAX(date)

greatest-n-per-group, join, mysql, sql

Solution

There are so many ways to solve this but I'm used to do it this way. An extra subquery is needed to separately calculate the latest `cDate` for every `ID`.

SELECT  a.*, c.*
FROM    theme a
        INNER JOIN notice_theme b
            ON a.ID = b.id_theme
        INNER JOIN  notice c
            ON b.id_notice = c.ID
        INNER JOIN
        (
            SELECT  a.id_theme, MAX(b.DATE_CREATE) max_date
            FROM    notice_theme a
                    INNER JOIN notice b
                        ON a.ID_Notice = b.ID
            GROUP   BY a.id_theme
        ) d ON  b.id_theme = d.id_theme AND
                c.DATE_CREATE = d.max_date

- SQLFiddle Demo

Problem

i have these tables : - notice - id INT - cdate DATETIME - ... - theme - id - name - notice_theme - id_notice - id_theme I want to get the latest notices for each theme. ``` SELECT id_theme, n.id FROM notice_theme LEFT JOIN ( SELECT id, cdate FROM notice ORDER BY cdate DESC ) AS n ON notice_theme.id_notice = n.id GROUP BY id_theme ``` The result is not good. An idea ? Thanks.

Original source