Saturday, June 21, 2008

SQL Server 2005- OVER() partition

This will be useful for GridView pagination:

An aggregate functions can be added to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function.

This means that this code:

   1: select 
   2:     o.customerID, o.productID, o.orderDate, o.orderAmount, t.Total
   3: from 
   4:     Orders o
   5: inner join 
   6:    (
   7:     select customerID, sum(orderAmount) as Total from Orders group by customerID
   8:    ) 
   9:   t on t.customerID = o.customerID
can be replaced with:
   1: select customerID,  productID, orderDate, orderAmount, 
   2:       sum(orderAmount) OVER (Partition by CustomerID) as Total
   3: from Orders


No comments:

Post a Comment