Select top distinct products based on a distinct column
I'd like to write a sql server 2008 query that selects the top distinct products by a brandId ordered by the latest date published as in the following table:
dbo.Products
ProductId, BrandId, PublishedOn
Table contents:
1, 1, 2011-01-01
2, 2, 2011-01-01
3, 3, 2011-01-01
4, 1, 2011-01-09
5, 1, 2011-01-10
6, 2, 2011-01-10
The output i'm after is:
6, 2, 2011-01-10
5, 1, 2011-01-10
3, 3, 2011-01-01
Anyone know how this can be achieved?
Apologies if the title is incorrec开发者_如何学JAVAt but i didnt quite know how to phrase this one.
Looks like you want the latest row for each BrandId
as ordered by PublishedOn
?
;WITH T
AS (SELECT *,
ROW_NUMBER() OVER (PARTITION BY BrandId
ORDER BY PublishedOn DESC) AS RN
FROM dbo.Products)
SELECT ProductId,
BrandId,
PublishedOn
FROM T
WHERE RN = 1
精彩评论