InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 03:14:01 GMT20http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxgood one :-)Fri, 07 Dec 2012 04:19:36 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxI would have thought not having an ifexists() on the first line would ensure this would fail (table not existing to drop), though I second guessed the question and got it right.Thu, 26 Feb 2009 20:18:16 GMTNugbyhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxI too got the same error, Please review this case ...Mon, 30 Jun 2008 05:50:54 GMTPRAVEEN_KUMAR_NALLAhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxExcellent question....Mon, 30 Jun 2008 02:22:50 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxThanks. I was able to run a quey in a DB with compatibility set to 90. The previous DB only has compatibility set up to 80.Fri, 27 Jun 2008 12:06:43 GMTthuybui-791943http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxExcellent QOD. I spent enough time studying the code and the results in QA that I don't feel guilty about running it before choosing the correct answer :Whistling:. The light bulb finally went on for me that including "subgrp" in the result set is a red herring of sorts -- none of the three counts change depending on its value. The results are easier to understand by removing subgrp and making them distinct. If we make the Select statement....[code]select distinct grp --,subgrp,count(*) OVER (partition by grp,subgrp) grpcount,count(*) OVER (partition by grp) subcount ,count(*) OVER (partition by NULL) grandcountfrom #windowsgroup by grp , subgrp[/code] Then our results are much more straight-forward and, to me anyway, easier to tie back to the query:[code]grp grpcount subcount grandcount----- ----------- ----------- -----------aaaaa 1 3 4bbbbb 1 1 4[/code]Putting the results in plain English, [code]there's 1 grp "aaaaa" with 3 subgrps out of 4 total subgrps,there's 1 grp "bbbbb" with 1 subgrp out of the same total 4 subgrps[/code] The grpcount will always be 1 since we did "group by" and windowed on the same combination of grp and subgrp.Fri, 27 Jun 2008 11:40:58 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspx[quote][b]thuybui (6/27/2008)[/b][hr]Yes, Microsoft SQL Server Management Studio 9.00.1399.00[/quote]And what is the compatibility level of the DB in question?Fri, 27 Jun 2008 11:18:13 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxthuybiMore than likely it is a typographical error, for example the following is missing the ) directly after the asterikselect grp,subgrp,count(* OVER Should beselect grp,subgrp,count(*[b])[/b]OVERFri, 27 Jun 2008 11:06:08 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxI really hate marking the wrong anwser selection letter after determing the right output. My hand eye coordination wasn't that good back in the days of PE either.-- Mark D Powell --Fri, 27 Jun 2008 10:55:42 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxYes, Microsoft SQL Server Management Studio 9.00.1399.00Fri, 27 Jun 2008 10:51:41 GMTthuybui-791943http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspx[quote][b]thuybui (6/27/2008)[/b][hr]I received this error while running the query?Msg 156, Level 15, State 1, Line 17Incorrect syntax near the keyword 'OVER'.Any idea?[/quote]Are you running this in 2005 "native"? meaning - in compatibility 90?Fri, 27 Jun 2008 10:43:07 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxI received this error while running the query?Msg 156, Level 15, State 1, Line 17Incorrect syntax near the keyword 'OVER'.Any idea?Fri, 27 Jun 2008 10:10:09 GMTthuybui-791943http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspx[quote]That's certainly a good way to describe it! Although this particular behavior is laid out in ANSI's definition, it's certainly not incredibly well documented elsewhere (and not mentioned per se on Ms' side), so I've actually seen it being disclaimed as "a bug". [/quote]Now this has me wondering how many other items MS tech writers of BOL have missed or failed to properly communicate with MS development teams about.Thanks, now I have to determine where to keep that link you provided for my own future referenceFri, 27 Jun 2008 09:59:11 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxAwesome question, I've actually used the Window functions in the past and the Group By almost got me!Fri, 27 Jun 2008 09:40:55 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspx[quote][b]Steven Cameron (6/27/2008)[/b][hr]I think the point of the question is that unless you know how windowed aggregation works, it is a gotcha.[/quote]That's certainly a good way to describe it! Although this particular behavior is laid out in ANSI's definition, it's certainly not incredibly well documented elsewhere (and not mentioned per se on Ms' side), so I've actually seen it being disclaimed as "a bug".Fri, 27 Jun 2008 07:22:09 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxI think the point of the question is that unless you know how windowed aggregation works, it is a gotcha.Fri, 27 Jun 2008 07:19:07 GMTSteven Cameronhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxThe gotcha is in the Windowed aggregate itself. It's a bit RBAR-like in that it seems to create a worktable with LOOP joins to the outer query, so its perf tends to suffer a lot with the "window" you're running this against is large. Meaning - if you were to need something like a "percentage of total", and your unsummarized data was large, it would likely be more efficient to use a derived table setup to create the "total", since you can control what method is used to link it into the main query.On the other hand, if you need quick access to different aggregates (especially ones aggregated against different groupings), and the groups aren't huge, it tends to do VERY well.Fri, 27 Jun 2008 07:18:04 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxGood question. I love learning new things and this is a new one on me. I can keep this for my bag of tricks. Is there a downside to doing this? The code is clean but my gut feeling is there may be some inefficiencies. Are there any gotchas?:discuss:Fri, 27 Jun 2008 06:54:54 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic524735-1269-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/63214/">Windowed Aggregate functions</A>[/B]Thu, 26 Jun 2008 21:52:01 GMTMatt Miller (#4)