mysql - MySQL查询排序

这是我的数据。我想取6行，但我希望所有HeadlineCategoryId在我的结果列表中都是唯一的。如果我选择前6行，我将从HeadlineCategoryID20（6,2）中选取2行。你对此有什么建议吗？

最佳答案

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  HeadlineCategoryID, MAX(Creation) max_date
            FROM    TableName
            GROUP   BY HeadlineCategoryID
        ) b ON  a.HeadlineCategoryID = b.HeadlineCategoryID AND
                a.Creation = b.max_date
ORDER   BY a.Creation DESC  -- << specify here how are you going to sort
LIMIT   6                   --    the records you want to get

更新1

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  HeadlineCategoryID, MAX(NewsID) max_id
            FROM    TableName
            GROUP   BY HeadlineCategoryID
        ) b ON  a.HeadlineCategoryID = b.HeadlineCategoryID AND
                a.NewsID = b.max_id
ORDER   BY a.Creation DESC  -- << specify here how are you going to sort
LIMIT   6                   --    the records you want to get