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
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CatId ORDER BY Posted DESC) AS rn
FROM Articles
) q
WHERE rn <= 5
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
精彩评论