>SQL Server: The Magical OVER clause


>

When comes to query writing, I think, I am so lazy 😉 and always prefer less code. Recently I found OVER clause so helpful to write my code in more effect and more magical way. Most of us know the usage of OVER clause, when creating row number column for a given partition or even with out any partition (especially when continuous row number is required for all records). 

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
,ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY ProductID) AS GroupRowNumber
,ROW_NUMBER() OVER(ORDER BY ProductID) AS ContRowNumber
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

But I think most magical part of OVER clause is, when we use aggregate functions for a given partition. Through this method we can avoid lengthy and complex sub queries. Here is an example from msdn library.
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO
Advertisements

Posted on March 8, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: