Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 2008 126
SQL Server 2005 109
TSQL Tips n Tricks 80
Performance Tuning 57
SQL Server 2012 48
SQL Server Basics 24
Sql Server Management 21
SSMS 21
Index 18
SQL Server Denali 13

Archive · View All
March 2011 24
December 2010 23
January 2011 22
May 2011 17
February 2011 16
April 2011 16
July 2012 12
August 2011 9
September 2012 8
August 2012 8

SQL Server: The Magical OVER clause

Mar 8 2011 1:32PM by aasim abdullah   

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

Tags: SQL Server 2008, #tsql, #SQL Server, SQL Server Basics, Tricks, brh, TSQL Tips,


aasim abdullah
23 · 7% · 2373
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • nice , great one

    commented on Jan 3 2012 11:48AM
    EB5 VIsa
    3398 · 0% · 1

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]