[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Finding the top 3 for each the Year

I have a dataset that looks something like this...

Product Name,  Manufacturer,  SaleYear,   GrossSales

I need to find the top three products sold by GrossSales for each year, so it would look like this...

2008
Sony, Product X, $20,000
HP, Product Y, $19,500
Dell, Product Q,  $18,000

2009
Apple, Product J,  $22,150
Dell, Product Q,   $19,200
Sony, Product X,  $14,000

2010...
...top three products....

etc...

So for each year, show the top three products sold by the Gross Revenue...

I believe this uses Row_Number() and Over but not sure of the format.

Thanks!
0
gdemaria
Asked:
gdemaria
1 Solution
 
appariCommented:
try this

Select Product Name,  Manufacturer,  SaleYear,   GrossSales
From (Select Product Name,  Manufacturer,  SaleYear,   GrossSales,
row_number() over(partition by SaleYear order by GrossSales desc) rowNum
from tableName) as A
where rowNum <=3
0
 
gdemariaAuthor Commented:
That was it, thanks very much!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now