CODEWITHSUNDEEP
sqlsql-server
Big_Dwarf90
Big_Dwarf90

Reputation: 77

Not getting the result that I need by using ROW_NUMBER()

I'm using advantureworks2017 and what I'm trying to get is the top 2 selling products by year,, what I have so far is this but it's showing me only the top 2 rows which is not what I need, I need the top 2 products in each year.

SELECT  TOP (2) ROW_NUMBER() OVER (ORDER BY sum(linetotal) DESC) ,
        ProductID,
        year(h.OrderDate) 'OrderYear'
from    Sales.SalesOrderDetail so
left outer join Sales.SalesOrderHeader h
on  so.SalesOrderID = h.SalesOrderID
group by    year(h.OrderDate), ProductID

Upvotes: 0

Views: 42

Answers (2)

Johnatix
Johnatix

Reputation: 21

When you ORDER your ROW_NUMBER by sum(linetotal) it's goning to fail if you have multiple sum(linetotal) which are equal.

I prefer to do it that way:

Declare table(number of columns = number of your query results columns + 1) fill first column in declared table with identity(1,1) and next insert query results into the rest columns.

Upvotes: 1

zealous
zealous

Reputation: 7503

Try to add row_number in the subquery and then use that rank <= 2 in the outer query to select top 2

select
  ProductID,
  OrderYear
from
(
  SELECT 
          ProductID,
          year(h.OrderDate) 'OrderYear',
          ROW_NUMBER() OVER (ORDER BY sum(linetotal) DESC)  as rnk
  from    Sales.SalesOrderDetail so
  left outer join Sales.SalesOrderHeader h
  on  so.SalesOrderID = h.SalesOrderID
  group by    year(h.OrderDate), ProductID
) val

where rnk <= 2

Upvotes: 2

Related Questions