Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using OVER with an Aggregate Function


Using OVER with an Aggregate Function

Author
Message
brdudley
brdudley
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1533 Visits: 1737
Hugo Kornelis (11/4/2010)

(If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:


One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.


Select StateProvinceID,
TaxType,
Max(TaxRate) Over (Partition by StateProvinceID),
Max(TaxRate) Over (Partition by StateProvinceID, TaxType)
from Sales.SalesTaxRate
Order by StateProvinceID


SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
mislamuddin (11/3/2010)
Hi:
If select distinct specified order by item must appear in the select list and there will be error message as follows:
Msg 145, Line 15 St 1


I did not get this error.
Of course I also did not get the differance between the first two answers. Cool

Nice question about a great feature!
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3352 Visits: 2844
Very good question - I learned something today.

Hugo
If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:


To look at the question and only talk about it's limitations makes one wonder why is it there.

brdudley
One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.

Excellent addition to the questions functionality. I learned something more, and have already started playing with tests to see about changes, especially in some complex reports.

Thanks

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Thanks for the question. I had never considered using the OVER in this way.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

cdesmarais 49673
cdesmarais 49673
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 1423
I worry that the DISTINCT is going to give people the impression that OVER/PARTITION does the same thing as GROUP BY. It's an important distinction and the explanation doesn't make it clear.
getoffmyfoot
getoffmyfoot
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 412
2 points! Woot
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 237
got Confused between across all state & each state and got it wrong...Unsure
tilew-948340
tilew-948340
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 2437
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16453 Visits: 13207
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).



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11572
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search