Geek asks and answers

Return 5 new articles for each category in MS SQL Server

Let's say I have a table Articlesin a SQL Server 2008 database with columns ID INT, Title VARCHAR(100), CatID INT, Posted DATETIME.

To get 5 new articles for a certain category, I can do this.

SELECT TOP (5) * FROM Articles WHERE CatID = @CatID ORDER BY Posted DESC

But what if I want 5 new articles for each category? I know that I can repeat the request above for each category, but is there a way to make one request that will return 5 new articles for each category?

EDIT:

Here is the actual query that I use to return 5 new articles with the @SectionID section. According to the actual terminology that I use, this is the “section” that I am grouping, not the “category”.

SELECT TOP (5) *
FROM Article
    INNER JOIN Subcategory on Article.ArtSubcategoryID = Subcategory.SubID
    INNER JOIN Category on Subcategory.SubCatID = Category.CatID
    INNER JOIN section ON Category.CatSectionID = Section.SecID
WHERE (Section.SecID = @SectionID)
ORDER BY Article.ArtUpdated DESC

EDIT 2:

And so the query that I came up with is based on the comments here. Everything seems to be in order.

SELECT  *
FROM (
    SELECT Article.*,
        ROW_NUMBER() OVER (PARTITION BY SecID ORDER BY ArtUpdated DESC) AS rn
    FROM Article
        INNER JOIN Subcategory on Article.ArtSubcategoryID = Subcategory.SubID
        INNER JOIN Category on Subcategory.SubCatID = Category.CatID
        INNER JOIN section ON Category.CatSectionID = Section.SecID
) q
WHERE rn <= 5
+3
2
SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY CatId ORDER BY Posted DESC) AS rn
        FROM    Articles
        ) q
WHERE   rn <= 5
+4

;WITH CTE AS (SELECT ROW_NUMBER() OVER(PARTITION BY CatID ORDER BY Posted DESC) 
AS Rownum,*
FROM Articles )

SELECT * FROM CTE WHERE Rownum <= 5
+3

More articles:


All Articles