Search code examples
sqlamazon-redshiftwindow-functions

SQL - Use dense_rank and group by together

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

Solution

  • 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.