Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Nakul Vachhrajani » Using OVER with an Aggregate Function
Add to briefcase
33 posts, Page 1 of 4
1234»»»

Using OVER with an Aggregate Function Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Nakul Vachhrajani
Posted Wednesday, November 03, 2010 8:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:02 AM
Points: 1,277, Visits: 1,609
Comments posted to this topic are about the item Using OVER with an Aggregate Function

Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1015661
mislamuddin
Posted Wednesday, November 03, 2010 10:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 10, 2011 5:51 AM
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
Post #1015676
Iulian -207023
Posted Thursday, November 04, 2010 2:13 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: Saturday, March 16, 2013 9:53 AM
Points: 847, Visits: 768
Nice question, thank you.
It works for me.

Iulian
Post #1015717
Imran Ashraf-452633
Posted Thursday, November 04, 2010 2:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:54 AM
Points: 1,103, Visits: 411
Good question learned something new today.
Post #1015718
Hugo Kornelis
Posted Thursday, November 04, 2010 3:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 5,244, Visits: 7,063
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
Post #1015738
Hugo Kornelis
Posted Thursday, November 04, 2010 3:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 5,244, Visits: 7,063
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
Post #1015740
Toreador
Posted Thursday, November 04, 2010 3:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:52 AM
Points: 1,356, Visits: 4,761
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"?
Post #1015742
Nils Gustav Stråbø
Posted Thursday, November 04, 2010 6:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 23, 2013 8:52 AM
Points: 1,788, Visits: 3,327
Goo question, thanks, but I don't understand why this was a two point question...
Post #1015799
Daniel Bowlin
Posted Thursday, November 04, 2010 7:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:10 PM
Points: 2,673, Visits: 2,418
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.
Post #1015836
brdudley
Posted Thursday, November 04, 2010 7:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,235, Visits: 1,397
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.
Post #1015870
« Prev Topic | Next Topic »

Add to briefcase
33 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.