CODEWITHSUNDEEP
sql-serversql-server-2008
Jonathan Wood
Jonathan Wood

Reputation: 67251

Return 5 Newest Articles for Each Category in MS SQL Server

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

To get the 5 newest articles for a particular category, I can do this.

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

But what if I want the 5 newest articles for each category? I know I can repeat the query above for each category, but is there any way to do a single query that will return the 5 newest articles for each category?

EDIT:

Here's is the actual query I'm using to return the 5 newest articles with the section @SectionID. According to the actual terminology I'm using, it's the "section" I'm grouping by, not "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 here's the query I came up with based on comments here. Seems to work okay.

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

Upvotes: 3

Views: 161

Answers (2)

SQLMenace
SQLMenace

Reputation: 135111

Try this

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

SELECT * FROM CTE WHERE Rownum <= 5

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425613

SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY CatId ORDER BY Posted DESC) AS rn
        FROM    Articles
        ) q
WHERE   rn <= 5

Upvotes: 4

Related Questions