InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 19:19:44 GMT20http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]zombieisdead2020 (1/29/2013)[/b][hr]I see first_value and last_value function, but do not see second or third value function!How can I write for example second_value() function by using analytical window function?[/quote]You will probably have to revert to the ROW_NUMBER() function, then look for where ROW_NUMBER() = 2, 3, etc.Tue, 29 Jan 2013 21:29:28 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxI see first_value and last_value function, but do not see second or third value function!How can I write for example second_value() function by using analytical window function?Tue, 29 Jan 2013 12:07:00 GMTzombieisdead2020http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxGreat article Wayne, thanks.Tue, 24 Jan 2012 06:01:42 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxGreat article Wayne!Thanks for sharing.Sun, 22 Jan 2012 16:58:46 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxNow if only we could get 2012 on our production boxes before 2020. So excited for SS2012Sat, 21 Jan 2012 16:24:06 GMTGabe T.http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxWonderful article, Wayne. The charts summarize a nicely laid out and well written article and make it real easy to get extra information. Thanks for taking the time to write a class article!Fri, 20 Jan 2012 09:56:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]WayneS (1/19/2012)[/b][hr]Actually...this article only talks about the Analytic functions. You might want to look at my previous article [url=http://www.sqlservercentral.com/articles/T-SQL/75466/][u]The OVER Clause enhancements in SQL Server code named “Denali”, CTP3[/u][/url].Basically, the MIN/MAX functions DO allow you to order your partition by a different column. But let's talk about this "over there"... it's more appropriate there.[/quote]Thanks for your link the the other article. I was not aware that these were also upgraded.Fri, 20 Jan 2012 00:53:25 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxJason, Geoff, Mark, Tim and KWymore...Thanks, I'm very glad that you'll like it.Thu, 19 Jan 2012 19:53:25 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b] Jack Corbett (1/19/2012)[/b][hr]Good article Wayne. One thing I like to see in articles like this though is how you might solve the same problem without using the new functions. Just to see how much the new functions help.[/quote]Thanks Jack. That is a very good idea... I'll have to keep it in mind.Thu, 19 Jan 2012 19:51:43 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]Zeev Kazhdan (1/19/2012)[/b][hr]Finally they [b]will[/b] have what Oracle [b]had[/b] delivered ages ago....[/quote]and MySql, and DB2, and... well, nearly everyone else. It is long overdue, and should have been in 2008.Thu, 19 Jan 2012 19:48:57 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]j-1064772 (1/19/2012)[/b][hr]How do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?[/quote]The Analytic functions don't, but the OVER clause enhancements allows running totals with the SUM() function and the enhanced OVER clause. See my previous article: [url=http://www.sqlservercentral.com/articles/T-SQL/75466/][u]The OVER Clause enhancements in SQL Server code named “Denali”, CTP3.[/u][/url] You might also want to see my blog post on this: [url=http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/][u]http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/[/u][/url]. In short, the QU still wins, but the newly enhanced OVER clause beats everything else.Did you click the "TSQL Challenges Winner" icon in my signature? It beat everything else quite handily doing a running total challenge.Thu, 19 Jan 2012 19:47:47 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]RichB (1/19/2012)[/b][hr]Any idea what the performance of these things is like?[/quote]Did you click on that "TSQL Challenges Winner" icon in my signature? (Well, that is using the aggregate functions and not the analytic, but I believe them to be similar.)Thu, 19 Jan 2012 19:42:09 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]Christian Buettner-167247 (1/19/2012)[/b][hr][quote][b]WayneS (1/18/2012)[/b][hr]Comments posted to this topic are about the item [B]&lt;A HREF="/articles/SQL+Server+2012/76704/"&gt;The new Analytic functions in SQL Server 2012&lt;/A&gt;[/B][/quote]Great information, thanks for that.To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end [code="sql"]MAX(x) OVER (PARTITION BY y)[/code] should return the same as [code="sql"]FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)[/code]But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)So the whole point of FIRST_VALUE and LAST_VALUE seem to be:[code="sql"]FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY [b]SomeOtherColumn[/b] DESC)[/code]Any other points I may have missed?[/quote]Actually...this article only talks about the Analytic functions. You might want to look at my previous article [url=http://www.sqlservercentral.com/articles/T-SQL/75466/][u]The OVER Clause enhancements in SQL Server code named “Denali”, CTP3[/u][/url].Basically, the MIN/MAX functions DO allow you to order your partition by a different column. But let's talk about this "over there"... it's more appropriate there.Thu, 19 Jan 2012 19:40:45 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxNice article, very helpful. It's nice to see articles like this with examples. Definitely helps to prove to others why upgrades are needed. :)Thu, 19 Jan 2012 15:27:29 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxThanks - this is a great summary.Thu, 19 Jan 2012 13:22:59 GMTtim.hulsehttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxHow do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?Thu, 19 Jan 2012 13:14:36 GMTj-1064772http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxThank you for this Wayne - I'll definitely come back to it later. I like your concise writing style with helpful examples too. - MarkThu, 19 Jan 2012 12:41:25 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxGood article Wayne. One thing I like to see in articles like this though is how you might solve the same problem without using the new functions. Just to see how much the new functions help.Thu, 19 Jan 2012 08:30:24 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxPERCENTILE_CONT / PERCENTILE_DISC Lets recall from statistics that continuous variables (PERCENTILE_CONT) are those that "cannot not be exactly counted," while discrete variables (PERCENTILE_DISC) "have an exact amount."PERCENTILE_CONT would be better used when performing estimation or predictive calculations, as you are trying to determine a value from a sample of data (where the entire data set is unknown).PERCENTILE_DISC would be better for when we need an exact measure, and the entire data set is known.**Note: I am not a statistician, nor an analytics guru, so hopefully someone with greater experience could shine some better light on this, or at least validate my statement. Hopefully this helps some trying to understand the purposes behind these functions.StephenThu, 19 Jan 2012 08:24:53 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxGreat article Wayne. This is great info to have when management wants to know why we are always upgrading :-DThu, 19 Jan 2012 08:14:18 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxGood Stuff WayneThu, 19 Jan 2012 07:55:42 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxAny idea what the performance of these things is like?Thu, 19 Jan 2012 06:48:25 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspx[quote][b]WayneS (1/18/2012)[/b][hr]Comments posted to this topic are about the item [B]&lt;A HREF="/articles/SQL+Server+2012/76704/"&gt;The new Analytic functions in SQL Server 2012&lt;/A&gt;[/B][/quote]Great information, thanks for that.To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end [code="sql"]MAX(x) OVER (PARTITION BY y)[/code] should return the same as [code="sql"]FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)[/code]But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)So the whole point of FIRST_VALUE and LAST_VALUE seem to be:[code="sql"]FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY [b]SomeOtherColumn[/b] DESC)[/code]Any other points I may have missed?Thu, 19 Jan 2012 06:01:54 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxI finally see SQL Server catching up, I am so used to of Lead/Lag in DB2. It will make my life little easier. ThanksThu, 19 Jan 2012 05:53:14 GMTETL_Guyhttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxFinally they [b]will[/b] have what Oracle [b]had[/b] delivered ages ago....Thu, 19 Jan 2012 02:39:30 GMTZeev Kazhdanhttp://www.sqlservercentral.com/Forums/Topic1238434-1273-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SQL+Server+2012/76704/">The new Analytic functions in SQL Server 2012</A>[/B]Wed, 18 Jan 2012 22:08:15 GMTWayneS