Tuesday, 12 April 2016

Group By Vs Partition By

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

They're used in different places. group by modifies the entire query, like:
select customerId,Name,number count(*) as orderCount
from Orders
group by customerId,Name,number 

But partition by just works on a window function, like row_number:
select row_number() over (partition by customerId order by orderId)
    as rowNum, customerId,Name,number over (partition by customerId order by orderId)
from Orders where rowNum=1

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.

In this first query the full column will get grouped  and it will return the grouped results but in second query only the selected column get grouped and returns all results so here the rowNum used to get the correct result as expected.

Have any queries comment here :)

No comments:

Post a Comment