CODEWITHSUNDEEP
sqlamazon-redshiftwindow-functions
user12562215
user12562215

Reputation: 145

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

Upvotes: 2

Views: 8551

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Your query is basically equivalent to:

select pt.*,
       dense_rank() over (partition by type order by total_sales desc) as rnk 
from (select product, type, sum(sales) as total_sales
      from mytable 
      where date > dateadd(month, -3, getdate()) 
      group by product, type
     ) pt;

That is, the dense_rank() is performed after the GROUP BY.

This is totally allowed. So, your understanding that these cannot be used together is incorrect. However, there are limitations. The references in the dense_rank() need to be columns or expressions that are known after the GROUP BY.

So, this is allowed in your version:

dense_rank() over (partition by type order by sum(sales) desc) as rnk

but this is not:

dense_rank() over (partition by type order by sales desc) as rnk

because sales (by itself) is not valid after the group by.

Upvotes: 4

Data_Enginner_Suraj
Data_Enginner_Suraj

Reputation: 76

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.

Upvotes: 1

Related Questions