SQL Clone
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.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2793 Visits: 8084
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 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 (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 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.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 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
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 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
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14402 Visits: 12213
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 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
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: 1501 Visits: 1245
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4148 Visits: 7865
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 Veteran
    SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

    Group: General Forum Members
    Points: 227 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