Thursday 7 June 2012

Difference between GROUP BY and PARTITION BY


GROUP BY modifies the entire query, like:
Select customerId, count(*) as orderCount 
from Orders 
group by customerId 

But PARTITION BY just works on a window function, like row_number:
select row_number() over (PARTITION BY customerId order by orderId) 
    as OrderNumberForThisCustomer 
from Orders 
A Group By normally reduces the number of rows returned by rolling them up and 
calculating averages or sums for each row.  Partition By does not affect the number of rows 
returned, but it changes how a window function's result is calculated.



No comments:

Post a Comment