CODEWITHSUNDEEP
sql-serversql-server-2008greatest-n-per-group
sf.
sf.

Reputation: 25518

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 incorrect but i didnt quite know how to phrase this one.

Upvotes: 1

Views: 586

Answers (1)

Martin Smith
Martin Smith

Reputation: 453930

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

Upvotes: 2

Related Questions