显示最新结果的MySQL分组依据

Question

我正在尝试查询MySQL来订购，然后分组...这是一个在这里经常出现的问题，我找到了一个与我相关的答案：Getting a MySQL group by query to display the row in that group with the highest value

然而，我发现在进行分组之前，它仍然没有排序。

具体来说，我想要做的是使用Wordpress自定义的帖子类型，按照一个名为' date‘的元数据域进行分组，按帖子的日期排序。

下面是我的问题：

SELECT 
    `ID`, `date`, `post_date`, `date_rank`
FROM (
        SELECT
            `Post`.`ID`,
            `Post`.`post_date`,
            `PostData`.`meta_value` AS `date`,
            CASE
                WHEN @data_date = `PostData`.`meta_value` THEN @rowum := @rownum + 1
                ELSE @rownum := 1
            END AS date_rank,
            @data_date := `PostData`.`meta_value`
        FROM
            `".$this->wpdb->posts."` AS `Post`
        JOIN 
            `".$this->wpdb->postmeta."` AS `PostData`
            ON `Post`.`ID` = `PostData`.`post_id` AND `PostData`.`meta_key` = 'date'
        JOIN (SELECT @rownum := 0, @data_date := '') AS `vars`
        WHERE 
            `Post`.`post_type` = 'my_post_type'
            AND
            `Post`.`post_status` = 'publish'
        ORDER BY `Post`.`post_date` DESC
    ) AS `x`
WHERE date_rank = 1
ORDER BY `date` ASC

期望的结果是每个“日期”(这是一个元字段)的帖子，以及根据post_date的这个“日期”的最新帖子。

Answer 1

SELECT  *
FROM    (
        SELECT  DISTINCT meta_value
        FROM    postdata pd
        WHERE   pd.meta_key = 'date'
        ) pd
JOIN    post p
ON      p.id = 
        (
        SELECT  post_id
        FROM    postdata pdi
        JOIN    post pi
        ON      pi.id = pdi.post_id
        WHERE   pdi.meta_key = 'date'
                AND pdi.meta_value = pd.meta_value
        ORDER BY
                pi.post_date DESC, pi.id DESC
        LIMIT 1
        )