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 belowTotalOrderQty: 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 NAME) AS [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 NAME) AS [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(4) OVER (ORDER BY NAME) AS [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:

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

ReplyDeleteThanks for sharing this. This a new technique I learnt today.

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

ReplyDeletepretty Amazing stuff!!

ReplyDeleteThank you Nelson. This overview has been helpful and inspiring.

ReplyDeleteThanks a lot for saving my day.

ReplyDeleteHi Nelson,

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

Thanks,

Marcos

Please review the post as it misses VERY important things like (to mention only a few of them):

ReplyDelete1. 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.

Nice

ReplyDeleteThank you for share this informative post.

ReplyDeleteExcellently 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.

ReplyDeletesoftware translation

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.

ReplyDeleteex: SELECT QueryRecordResultCount = COUNT(*) OVER (), RowNumber = ROW_NUMBER() OVER (..........), * FROM (...)

ReplyDeleteThis content creates a new hope and inspiration with in me. Thanks for sharing article like this. The way you have stated everything above is quite awesome. Keep blogging like this. Thanks.

SAP training in Chennai

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

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

DeleteID 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

http://khairyayman.eklablog.com/http-emc-mee-com-transfer-furniture-almadina-almonawara-html-a126376958 شركة نقل عفش بالمدينة المنورة

ReplyDeletehttp://khairyayman.eklablog.com/http-emc-mee-com-transfer-furniture-jeddah-html-a126377054 شركة نقل عفش بجدة

http://khairyayman.eklablog.com/http-emc-mee-com-movers-in-riyadh-company-html-a126376966 شركة نقل عفش بالرياض

http://khairyayman.eklablog.com/http-www-east-eldmam-com-a126377148 شركة نقل عفش بالدمام

https://forums.ipa.edu.sa/vb4/member.php?202-%D9%85%D8%AD%D9%85%D8%AF-%D8%AE%D9%8A%D8%B1%D9%8A

https://community.ebay.com/t5/Shipping-Returns/%D8%B4%D8%B1%D9%83%D8%A7%D8%AA-%D9%86%D9%82%D9%84-%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6-%D8%A7%D9%84%D9%85%D8%AA%D8%AD%D8%AF%D8%A9/qaq-p/26773546#M143585

ReplyDeletehttps://community.ebay.com/t5/Shipping-Returns/%D8%B4%D8%B1%D9%83%D8%A7%D8%AA-%D9%86%D9%82%D9%84-%D8%B9%D9%81%D8%B4-%D9%88%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A8%D8%A7%D9%84%D9%85%D9%85%D9%84%D9%83%D8%A9-%D8%A7%D9%84%D9%85%D8%AA%D8%AD%D8%AF%D8%A9/qaq-p/26768794#M143546

http://jumperads.unblog.fr/2017/03/22/%d8%a7%d9%81%d8%b6%d9%84-%d8%b4%d8%b1%d9%83%d8%a9-%d9%86%d9%82%d9%84-%d8%b9%d9%81%d8%b4-%d8%a7%d9%84%d9%82%d8%b5%d9%8a%d9%85/

http://jumperads.unblog.fr/2017/03/20/%d8%a7%d9%87%d9%85-%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d8%ae%d9%85%d9%8a%d8%b3-%d9%85%d8%b4%d9%8a%d8%b7/

http://jumperads.unblog.fr/2017/03/20/%d8%b4%d8%b1%d9%83%d8%a9-%d8%a7%d9%84%d9%85%d8%aa%d8%ad%d8%af%d8%a9-%d9%85%d9%86-%d8%a7%d9%87%d9%85-%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d9%85%d9%83/

http://jumperads.unblog.fr/2017/03/20/%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%a7%d8%ab%d8%a7%d8%ab-%d8%a8%d8%a7%d9%84%d8%b1%d9%8a%d8%a7%d8%b6-%d9%85%d9%88%d9%82%d8%b9-%d8%a7%d9%84%d9%85%d8%aa%d8%ad%d8%af%d8%a9/

ReplyDeletehttp://jumperads.unblog.fr/2017/03/23/%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d8%ad%d8%a7%d8%a6%d9%84-2017/

http://jumperads.unblog.fr/2017/03/23/%d8%b4%d8%b1%d9%83%d8%a9-%d8%aa%d9%86%d8%b8%d9%8a%d9%81-%d9%85%d8%aa%d8%ae%d8%b5%d8%b5%d8%a9-%d9%85%d8%af%d9%8a%d9%86%d8%a9-%d8%a7%d9%84%d9%82%d8%b7%d9%8a%d9%81/

http://jumperads.unblog.fr/2017/03/23/%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d9%8a%d9%86%d8%a8%d8%b9/

http://jumperads.unblog.fr/