I was under the impression that when we use rank/row_number/dense_rank, we can NOT use group by, but why does below logic run successfully
Select product, type, dense_rank() over (partition by type order by sum(sales) desc) as rnk
From mytable
Where date> dateadd(month, -3, getdate())
Group by product, type
Why am I able to order by sum(sales) within the rank function?
Thanks
All the columns which you have used in select statement, it is contained in either an aggregate function or the group by clause.
we can use rank function and group by in the same query set but all the columns should be contained in either aggregate function or the Group by clause.
So this query set is giving result by grouping Product and type and giving rank based on highest to lowest sales amount because you have used descending in Order by clause.