Using OVER with an Aggregate Function

Question

Post reply

Using OVER with an Aggregate Function

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 1

getoffmyfoot

Hall of Fame

Points: 3338

November 4, 2010 at 11:42 am

#1246729

2 points! Woot

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 2

got Confused between across all state & each state and got it wrong...:unsure:

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 3

My engish beeing what it is, I did also not get the differance between the first two answers, but that's ok. I learned something new and it is the important thing. Thank you!

I am not sure of what "over" does thought...and yes, I was starting to thing that it does the same thing than a group by.

So, what would be the first purpose of using an Over(partition by) function?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

tilew-948340 (11/4/2010)
My engish beeing what it is, I did also not get the differance between the first two answers, but that's ok. I learned something new and it is the important thing. Thank you!
I am not sure of what "over" does thought...and yes, I was starting to thing that it does the same thing than a group by.
So, what would be the first purpose of using an Over(partition by) function?

First of all, nice question. I was remembered of the fact that OVER() can also be used with other functions except ranking functions.

To answer tilews question: most of the time (in my case that is :-)), OVER() and partition by is used with ranking functions.

An example:

SELECT * FROM

(SELECT

column1

,column2

...

,

ROW_NUMBER() OVER(PARTITION BY myBusinessKey ORDER BY Priority DESC) AS RID

FROM myTable) tmp

WHERE RID = 1

This code will select all the rows from a table, partition them by business key, order it by some column that designates a priority (doesn't have to be, but just for the sake of the example) and then selects every row with a row_number of 1. This will select all the rows with unique values of the business key and with the highest priority (thus returning no duplicates).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

tilew-948340 (11/4/2010)
My engish beeing what it is, I did also not get the differance between the first two answers, but that's ok. I learned something new and it is the important thing. Thank you!
I am not sure of what "over" does thought...and yes, I was starting to thing that it does the same thing than a group by.
So, what would be the first purpose of using an Over(partition by) function?

Hi Tilew,

If you take out the DISTINCT from the SELECT (and/or add a few extra columns), it becomes obvious. The MAX(...) OVER(PARTITION BY ...) gives you the maximum of a group in each row. In this case, without the DISTINCT, you would get one row for each SalesTaxRate, and each row would include the maximum TaxRate for the StateProvinceID of that row. Before we had the OVER() clause for aggregates, this could only be achieved with a subquery or a join with a derived table.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Toreador SSChampion Points: 11365 More actions · Answer 6

tilew-948340 (11/4/2010)
My engish beeing what it is, I did also not get the differance between the first two

Don't worry about it, I've lived in England for all 47 years of my life and didn't get the distinction either 😀

SanDroid SSChampion Points: 10068 More actions · Answer 7

Toreador (11/5/2010)
tilew-948340 (11/4/2010)
My engish beeing what it is, I did also not get the differance between the first two
Don't worry about it, I've lived in England for all 47 years of my life and didn't get the distinction either 😀

Born and Bread in Dallas, Texas and had the hard time telling the differanc.

I got it right, but it was a coin toss.

timothy bates SSC-Addicted Points: 471 More actions · Answer 8

SanDroid (11/5/2010)
Toreador (11/5/2010)
tilew-948340 (11/4/2010)
My engish beeing what it is, I did also not get the differance between the first two
Don't worry about it, I've lived in England for all 47 years of my life and didn't get the distinction either 😀
Born and Bread in Dallas, Texas and had the hard time telling the differanc.
I got it right, but it was a coin toss.

How funny I was born and I've bred in Sacramento, CA. Your difference is quite obvious. Dallas is length6 while Texas is length5. Maybe your neighbors in Oklahoma City, Oklahoma might have a harder time with the differences. Especially if they use varchar(8) for City/State.:smooooth:

UMG Developer SSChampion Points: 13482 More actions · Answer 9

Interesting question, I have never thought of using the DISTINCT/OVER combination instead of the standard GROUP BY to get a value.

Of course if you look at the execution plans the costs are split 68%/32%.

Here is what I get from the STATISTICS:

DISTINCT/OVER version:

(26 row(s) affected)

Table 'Worktable'. Scan count 3, logical reads 163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTaxRate'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 3 ms.

GROUP BY version:

(26 row(s) affected)

Table 'SalesTaxRate'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

So, triple the time, and almost 4 times as many logical reads. I guess I won't be using it for this purpose. (Not to mention it is much harder to read.)

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 10

Before we had the OVER() clause for aggregates, this could only be achieved with a subquery or a join with a derived table.

I am realy lucky: I am having a hard time getting the wright answer from a query that is very complicated, trying to manipulate the data with group by, with, subqueries, etc. and maybe my answer to it would be the over(partition) case that, yesterday, I did not know the existence ...

I'll have to work on it more, but at least I understand better...

Thanks Hugo

Thanks da-zero

... and ya, thanks to UK and US for the good laughs:-D

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Toreador (11/4/2010)
I knew what the answer was, and thought that "The maximum Tax Rates across all State/Province IDs" described it well. I supopose I should have read more closely and realised that "The maximum Tax Rates per State/Province ID" also described it well, then tried to work out the difference between the two statements. Though I'd probably have picked the wrong one anyway.
Should it have said "The maximum Tax Rate [singular] across all State/Province IDs"?

I was lucky. I didn't look at all the answers, just went for the first one that was obviously correct. If I'd read the other one, I would have had great difficulty deciding what the difference was supposed to be and ended up with only an even chance of getting it right because to me those two answer options mean exactly the same. Now if the second had used singular instead of plural it would indeed have meant something quite different and been wrong, but as it stands I believe they both mean the same and both are correct.

edit: failure of finger-brain coordination

Tom

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 12

UMG Developer (11/5/2010)
So, triple the time, and almost 4 times as many logical reads. I guess I won't be using it for this purpose. (Not to mention it is much harder to read.)

Thanks for nice statistics.

Nice question.

Thanks

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 13

Thank you UMG, undeed great statistics.

Looking at the alternative with nested select I tend to chose the GROUP OVER, it seems to me more elegant.

Regards,

Iulian

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Hugo Kornelis (11/4/2010)
mislamuddin (11/3/2010)
If select distinct specified order by item must appear in the select list
It does. The code window has a scroll bar at the bottom; you'll find the column sstr.StateProvinceID to the far right.

Boy do I feel stupid - I fell at the same hurdle - didn't scroll across! Doh!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Gopi S Ten Centuries Points: 1091 More actions · Answer 15

Hi,

How do we filter the result using WHERE and HAVING clause as used in group by

Say like...

select MAX(TaxRate) AS 'Tax Rate',StateProvinceID

from Sales.SalesTaxRate where Name <> 'Canadian GST'

group by StateProvinceID

having StateProvinceID IN (1,6,7,9)

order by StateProvinceID

Thanks for posting this..