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 Friday, November 5, 2010 3:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 1,714, Visits: 6,259
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
Post #1016421
Posted Friday, November 5, 2010 7:24 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
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.
Post #1016529
Posted Friday, November 5, 2010 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:09 AM
Points: 216, Visits: 118
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.
Post #1016642
Posted Friday, November 5, 2010 3:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #1016814
Posted Friday, November 5, 2010 8:34 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
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
Post #1016848
Posted Saturday, November 6, 2010 4:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1016899
Posted Tuesday, November 9, 2010 1:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:09 AM
Points: 1,039, Visits: 1,309
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
Post #1017668
Posted Wednesday, November 10, 2010 2:55 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:54 AM
Points: 910, Visits: 840
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
Post #1018978
Posted Tuesday, November 16, 2010 7:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,786, Visits: 5,682
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1021507
    Posted Thursday, November 25, 2010 7:20 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: 2 days ago @ 4:40 PM
    Points: 157, Visits: 289
    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..
    Post #1026480
    « Prev Topic | Next Topic »

    Add to briefcase «««1234»»

    Permissions Expand / Collapse