开发者

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  
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜