MySql query ordering

Question

Here is my data. I want to take 6 rows, but I want all HeadlineCategoryId's to be unique in my result list. If I select the top 6 I would take 2 rows from HeadlineCategoryID 20 (6,2). Do you have any suggestions about it?

Answer 1

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 

---

UPDATE 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 

Answer 2

It looks like you want the six most recent records, but unique by HeadlineCategoryId. If so, this will work:

select top 6 NewsId, Creation, HeadlineCategoryId
from (select t.*,
             row_number() over (partition by HeadlineCategoryId order by Creation desc) as seqnum
      from t
     ) t
where seqnum = 1

As a note . . . This question originally indicated that it was using SQL Server, not MySQL. The solution in MySQL is not as simple. Here is one method with not exists:

select NewsId, Creation, HeadlineCategoryId
from t
where not exists (select 1
                  from t t2
                  where t2.HeadlineCategoryId = t.HeadlineCategoryId and
                        t2.id < t.id)
limit 6

The not exists portion is saying "where there is no other record with a larger id for a given headline category".