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
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1957 Visits: 2140
Comments posted to this topic are about the item Using OVER with an Aggregate Function

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
mislamuddin
mislamuddin
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
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
Iulian -207023
Iulian -207023
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1272 Visits: 1230
Nice question, thank you.
It works for me.

Iulian
Imran Ashraf-452633
Imran Ashraf-452633
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 484
Good question learned something new today.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

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


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8848 Visits: 11738
Imran Ashraf-452633 (11/4/2010)
Good question learned something new today.

(Not targeting you specifically, but everyone who posted and/or will post similar comments)
The question is good indeed - but please don't learn too much from this.
If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKSUM, 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:

SELECT MAX(TaxRate), StateProvinceID
FROM Sales.SalesTaxRate
GROUP BY StateProvinceID
ORDER BY StateProvinceID;


Much easier to understand and maintain in the future, and a more efficient execution plan to boot!

(EDIT: Fixed a silly typo - thanks, Oleg, for the heads-up!)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Toreador
Toreador
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2519 Visits: 8064
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"?
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 Visits: 3575
Goo question, thanks, but I don't understand why this was a two point question...
Daniel Bowlin
Daniel Bowlin
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3276 Visits: 2629
I have never used the OVER function before. I had to do a bit of reading to understand the question. I learned about a useful, but limited, function. Good question, thanks.
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: 1543 Visits: 1739
The existence of the Distinct in the query confused me, at first.

The key to using the Over as opposed to doing a Group By is really when you want to see all the rows in the group, but include an aggregate value.

With this example, where the only other field being displayed is the StateProvinceID, multiple rows for the same State add little value -- this is likely where the Distinct comes in.

I have used the Over clause for Row_Number, but not for aggregates, so this question gave me a few things to explore.
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