Pages

Monday 10 September 2012

SQL Server : Usage of OVER Clause

Over  clause can be used in association with aggregate function and ranking function. The over clause determine the partitioning and ordering of the records before associating with aggregate or ranking function. Over by clause along with aggregate function can help us to resolve many issues in simpler way. Below is a sample of Over clause along with the aggregate function.

SELECT 
SalesOrderID
,p.Name AS ProductName
,OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS TotalOrderQty
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg Qty of Item"     ,COUNT(OrderQty)OVER(PARTITION BY SalesOrderID) AS "Total Number of Item"     
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min order Qty"     
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max Order Qty" 
FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product p ON SOD.ProductID=p.ProductID WHERE SalesOrderID IN(43659,43664)

The Partition clause tell the aggregate function that the result should  be based on the salesorderid. The output will looks like as given below


TotalOrderQty: is the total quantity of product ordered in the the sales order.
Avg Qty of Item : is the average of order quantity for a salesorder. In our case Totalorderqty for the salesorderid 43659 is 26 and we have twelve order line . So the average quantity per order line = 26/12
Total Number of Item : is the number of product ordered in a salesorder.
Min Order Qty : is the minimum quantity ordered in a salesorder.
Max Order Qty: is the maximum quantity ordered in a salesorder.

The difference between group by and this method is , in group by we will get only the summery part. In our case if we use group by,will get only two records in the output. To get the result as above using group by, we need to write the query as given below:

SELECT 
p.name,GRPRESULT.
FROM sales.SalesOrderDetail SOD INNER JOIN Production.Product p ON SOD.ProductID=p.ProductIDINNER JOIN 
(
     SELECT
     
SalesOrderID
    
,SUM(OrderQty) AS TotalOrderQty
    
,AVG(OrderQty) AS "Avg Qty of Item"     

    ,COUNT(OrderQty)AS "Total Number of Item"       
    ,MIN(OrderQty) AS "Min order Qty"           
   ,MAX(OrderQty) AS "Max Order Qty" FROM Sales.SalesOrderDetail WHERE SalesOrderID               IN(43659,43664)GROUP BY SalesOrderID
GRPRESULT 
ON  GRPRESULT .SalesOrderID =sod.SalesOrderID  


Another interesting part is we can use the over clause with out partition clause which will do an aggregation on entire result set . Let us assume that we have requirement to list all sales order for the year 2008 with sales order number, total amount and Percentage of  2008 sales. It can be achieved easily as given below.

USE AdventureWorks2008
GO

SELECT 
SalesOrderNumber,
TotalDue,
(
TotalDue*100.)/  SUM(TotalDue) OVER()  AS [%2008Sales]

FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate)=2008

In SQL server 2012 there are more options along with over clause to display cumulative total .

ROW_NUMBER, RANK, DENSE_RANK and NTILE are the ranking function which can be used along with Over clause. For ranking function along with Partition by clause, we can use Order by clause also.To explain the rank function let us have a small table 

USE 
mydb
GO

CREATE TABLE Student
(
   
Name VARCHAR(10)
)

INSERT INTO Student VALUES ('aa'),('bb'),('cc'),('dd'),('ee')
INSERT INTO Student VALUES ('aa'),('bb'),('cc')
INSERT INTO Student VALUES ('aa'),('bb'),('cc')
INSERT INTO Student VALUES ('dd'),('ee')
INSERT INTO Student VALUES ('dd'),('ee')
INSERT INTO Student VALUES ('ff'),('gg'),('hh')

Row_Number() can be used in many scenarios like to filter the records, remove the duplicated records , implementing paging etc. Let us assume that we need to generate serial number while listing the entries from the student table.
SELECT ROW_NUMBER() OVER (ORDER BY NAMEAS [Si No],* FROM Student 

To remove the duplicate entries from the above table 
WITH cte_s
AS (
   
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY NAMEAS [SiNo],* FROM Student 

  )
DELETE FROM cte_s WHERE [SiNo]<>1
GO
SELECT FROM Student 

Let us assume that we have to divide the student into four group for a game. The NTILE will help us 

SELECT NTILE(4OVER (ORDER BY NAMEAS [Grpno],* FROM Student 

As the total number of records 18 is not divisible by 4, it has created two groups with 5 students and other two groups with 4 students.

Let us have slightly different table structure to understand RANK and DENSE_RANK function.

CREATE TABLE StudentMark
(
  
Name VARCHAR(10),
  
Mark INT

)
INSERT INTO StudentMark VALUES 
('aa',10),('bb',14),('cc',16),
('dd',22),('ee',25),('ff',25),
('gg',11),('hh',21),('ii',16)

To assign a rank to student based on their mark we can use the below query


SELECT RANK() OVER (ORDER BY mark DESC) AS 'Rank' ,* FROM StudentMark

The output will looks like as given below:

We can see that rank is assigned based on the position .We have two student with same marks and the student who has next highest marks came in the third position. This listing will be suitable for scenario like an entrance examination result for a total seat of 50. Student who has rank above 50 will not get the admission.
But some scenario we might need to display the actual rank with out any gap.The student who has the second highest mark should have the second rank irrespective of number of student have highest mark. The  below query will helps us to do that.

SELECT DENSE_RANK() OVER (ORDER BY mark DESC) AS 'Rank' ,*  FROM StudentMark 

The output will looks like as given below:

If you liked this post, do like my page on FaceBook  

35 comments:

  1. Just to point out that ROW_NUMBER() with OVER has been in since SQL 2005.

    ReplyDelete
  2. Thanks for sharing this. This a new technique I learnt today.

    ReplyDelete
  3. Thank you, this is exactly what I needed. This help me to save a lot of work

    ReplyDelete
  4. pretty Amazing stuff!!

    ReplyDelete
  5. Thank you Nelson. This overview has been helpful and inspiring.

    ReplyDelete
  6. Thanks a lot for saving my day.

    ReplyDelete
  7. Hi Nelson,

    Did you have any article who teach how to create a stoted procedure for query pagging with SQL Server 2008?

    Thanks,

    Marcos

    ReplyDelete
  8. Please review the post as it misses VERY important things like (to mention only a few of them):
    1. In SQL Server 2008 the execution plan reveals a lot of SELF JOINs, by power of 2 regarding the PARTITION BY,
    2. In SQL Server 2012 things are a little better, the SELF JOINs are diminished in the favor of ORDER BY but they are not completly gone!
    3. In SQL Server 2012 the OVER clause is largely extended as ORDER BY applies to much more functions as before.
    4. In SQL Server 2012 there is also a Windowing clause inside OVER: ROWS BETWEEN or RANGE BETWEEN.
    And so on.
    Please look at http://msdn.microsoft.com/en-us/library/ms189461.aspx.

    Personally I dislike this post because its superficiality.

    ReplyDelete
  9. Excellently amazing and exciting too.I am happy that at least somebody gave this subject an attention.This is really needy information for me and also for many other.Thanks for sharing this post.
    software translation

    ReplyDelete
  10. hi, i have found that the over clause for count on more than 1 cr records is taking more time in SQL 2012 than SQL 2008, can you please conform this.
    ex: SELECT QueryRecordResultCount = COUNT(*) OVER (), RowNumber = ROW_NUMBER() OVER (..........), * FROM (...)

    ReplyDelete
  11. Can we use the sum()over partition by in the where clause?

    ReplyDelete
    Replies
    1. For eg. if i have to check the sum of amt based upon the CustID in the below table is more than 50.

      ID CustID Amt
      1 11 45
      2 11 56
      3 11 67
      4 12 65
      5 13 45
      6 14 43
      7 14 22
      8 15 89
      9 15 67

      Delete
    2. No I guess because you can only use aggregate functions with Having (instead of Where) and Having goes along with GroupBy, since there is no Groupby you can't simply have a filter condition for aggregate function

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Best Wedding photographer in jaipur
    Great post and very helpful for me.Thanks for share this post and good post

    ReplyDelete
  14. gamekiller
    gamekiller app
    game killer
    gamekiller apk
    gamekiller download
    The crime was as senseless as it was cold-blooded. In the early morning hours of Oct 28, Marlowe Smith and Jamell Parker.

    ReplyDelete
  15. شركة تنظيف خزانات بجدة
    لو تريد حل قوي وفعال في التخلص من الحشرات في منزلك ليس امامك افضل من شركة مكافحة حشرات بجدة وفي حاجة إلى شركة رائدة تخلصك من الحشرات المنتشرة حولك فأليك الأن أقوى شركات منطقة جدة ومكة والطائف شركة مكافحة حشرات بجدة وكذلك في مكة المكرمة لدينا شركة مكافحة حشرات بمكة المكرمة ولدينا شركة رش مبيدات بالطائف متخصصة و شركة مكافحة حشرات بالطائف تعمل على مكافحة الحشرات المختلفة في المنازل والمزارع والحدائق والشوارع والفلل والقصور وغيرها من الأماكن المختلفة حيث توفر لك الخدمات اللازمة في حل مشاكلك الصعبة المتعلقة بالحشرات المنزلية.

    ReplyDelete
  16. memu for pc
    km player
    itunes

    Driod4x emulator
    Great post and very helpful for me.Thanks for share this post and good post

    ReplyDelete




  17. After this brings up its clients get entertained well with its premium services, the Female Escorts in Goais liable to increase the level of fun by its quality services dissimilar to get anywhere. So, let us forget everything and take pleasure in what you wait for a long time. Check our other Services...
    Female Escorts in Lucknow
    Housewife Escorts in Lucknow
    Independent Call Girls in Lucknow
    Lucknow Call Girls Service
    Lucknow Escorts Service
    Russian Escorts in Lucknow

    ReplyDelete
  18. If you are genuinely searching for a job oriented pega online training or pega online training hyderabad who are expertise to teach 100% practicals based course. And they provide certification material at pega training institutes in hyderabad and you can see this pega online training hyderabad. I was looking for a pega training institutes in pune whose instructor is really good at teaching. So you can either join at pega training institutes in Kolkata or pega training institutes in Bangalore in case if you are staying in Bengaluru. So start finding a job after a rigorous practice at pega training institutes in Mumbai whose faculty trainer the students at pega training institutes in Delhi also and in the end check out this pega interview questions.

    ReplyDelete
  19. This article will outline all the different strategies you should be aware of when it comes to soccer.

    Best IAS Coaching In India

    ReplyDelete
  20. Nice post. Thank you for this work. The travelers need to apply kenya multiple entry visa through online visa application. Check the details and read the guidelines before you fill up the application form.

    ReplyDelete
  21. I really liked your blog. Really looking forward to reading more. keep writing. India evisa for USA, US citizens are eligible to apply for Indian eVisa.The process is completely online and there is no need to submit the paperwork in person to any Indian Embassy or Consulate. And Within 5 to 10 minutes you can apply for your Indian visa for the USA.

    ReplyDelete
  22. Great job! I really like your work. US citizens are eligible to apply for the Indian eVisa. The process of Indian tourist visa for US citizens is fully online.

    ReplyDelete

  23. Thank you for the great article. Helpful article. Many people ask, Is Indian e visa open. Yes, you can now apply for an Indian e tourist visa online and travel in India.

    ReplyDelete
  24. Amazing really what i was looking for great stuff keep up the good work.. We provide Data entry services, If you have any query related to Data entry services then visit our website will get complete information related to your query.

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. western-overseas Punjab and Haryana whose foundation was laid in 2004 by Mr. Paradeep Balyan (Director) from a small consulting company in Ambala and with his efforts and hard work of many years, now, Western Overseas is known as the one of the Best study visa consultancy in North India. Things that make Western Overseas the best from the others are its experienced and qualified staff members, who provide the proper counselling of the suitable countries with each phase of the Student visa assistance. So, visit there and get free counselling and then choose your consultant.

    ReplyDelete
  27. This article is beautifully written! I'm hooked on your storytelling style! best seo services in gwalior

    ReplyDelete
  28. I really enjoyed this post! Great job on making it both informative and enjoyable to read. vehicle check

    ReplyDelete