Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Using OVER with an Aggregate Function Expand / Collapse
Author
Message
Posted Thursday, November 4, 2010 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:27 PM
Points: 1,370, Visits: 1,540
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

Post #1015884
Posted Thursday, November 4, 2010 8:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.

Nice question about a great feature!
Post #1015887
Posted Thursday, November 4, 2010 8:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #1015909
Posted Thursday, November 4, 2010 10:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 21,229, Visits: 14,937
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1015996
Posted Thursday, November 4, 2010 11:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 815, Visits: 1,078
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.
Post #1016063
Posted Thursday, November 4, 2010 11:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:36 AM
Points: 340, Visits: 406
2 points! Woot
Post #1016087
Posted Thursday, November 4, 2010 6:22 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
got Confused between across all state & each state and got it wrong...
Post #1016317
Posted Thursday, November 4, 2010 8:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
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?
Post #1016366
Posted Friday, November 5, 2010 2:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,283, Visits: 10,156
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1016398
Posted Friday, November 5, 2010 2:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 5,925, Visits: 8,174
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
Post #1016408
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse