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
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2419 Visits: 8064
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 :-D
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
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 :-D


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
timothy bates
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 124
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 :-D


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
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
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
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
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
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10745 Visits: 12019
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
Hardy21
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: 1204 Visits: 1399
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
Iulian -207023
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: 1198 Visits: 1226
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
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2274 Visits: 7827
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);




  • 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

  • Gopi S
    Gopi S
    SSC-Enthusiastic
    SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

    Group: General Forum Members
    Points: 183 Visits: 325
    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..
    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