InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 18:25:32 GMT20http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]sdolan 41249 (11/16/2012)[/b][hr]Our software manager indicated I could install SQL Server 2012 Express and be able to test out the new functions. Anybody have experience with that? I tried Express now I'm trying Express LocalDB and can seem to get the functions to operate.Any hints?Stephanie[/quote]Hi, Stephanie, and welcome to SSC. Your best shot at getting help with that would be to add a new topic in an appropriate forum. If you click the "Forums" link on the left side of the page you're looking at now, you'll see a good sized list from which to choose. I'd think you'd be best off now in the "SQL 2012 - General" forum ([url]http://www.sqlservercentral.com/Forums/Forum2799-1.aspx[/url]), under "SQL Server 2012".Fri, 16 Nov 2012 17:04:31 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxOur software manager indicated I could install SQL Server 2012 Express and be able to test out the new functions. Anybody have experience with that? I tried Express now I'm trying Express LocalDB and can seem to get the functions to operate.Any hints?StephanieFri, 16 Nov 2012 12:49:50 GMTsdolan 41249http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]john.arnott (11/2/2012)[/b][hr][quote][b]jlennartz (10/31/2012)[/b][hr]Not sure what real world value this question would be as it does not keep the sales attached to the correct UserName and UserID. Seems like you just corrupted your output.[/quote]You're absolutely correct that as is, the query would likely be sadly lacking in a real-world application. However, as I see it, the point was to distill the behavior of the LEAD() function, challenging the reader to consider how this new (in SQL 2012) feature works. I learned about a new tool in the chest here and, as I have said before in QOD discussions, was lookingfor a learning opportunity that goes beyond taking a quick look and stabbing at whatever answer feels right. May I suggest that you give yourself such an opportunity and construct a "real world" example of LEAD(). Spend a bit of time on it and consider publishing it as a new QOD or, perhaps better yet, a short article that would help other MS-SQL users understand this new function.[/quote][quote]You can usually learn something fro QOD, more times than not in the Discussions. That is why I follow QOD. And Thanks, John, for the suggestion, when we get SQL2012 I will consider doing that.[/quote]Mon, 05 Nov 2012 11:31:13 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxThere is one more link if anyone want to read about LEAD :[url=http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/][/url]Sat, 03 Nov 2012 03:00:28 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxNice explanation John...I dont have SQL Server 2012 but learned a new intresting thing.. :-)Sat, 03 Nov 2012 02:54:49 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]jlennartz (10/31/2012)[/b][hr]Not sure what real world value this question would be as it does not keep the sales attached to the correct UserName and UserID. Seems like you just corrupted your output.[/quote]You're absolutely correct that as is, the query would likely be sadly lacking in a real-world application. However, as I see it, the point was to distill the behavior of the LEAD() function, challenging the reader to consider how this new (in SQL 2012) feature works. I learned about a new tool in the chest here and, as I have said before in QOD discussions, was lookingfor a learning opportunity that goes beyond taking a quick look and stabbing at whatever answer feels right. May I suggest that you give yourself such an opportunity and construct a "real world" example of LEAD(). Spend a bit of time on it and consider publishing it as a new QOD or, perhaps better yet, a short article that would help other MS-SQL users understand this new function.Fri, 02 Nov 2012 15:02:47 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Hugo Kornelis (10/30/2012)[/b][hr][quote][b]john.arnott (10/30/2012)[/b][hr][quote][b]Raghavendra Mudugal (10/30/2012)[/b][hr]Just trying to understand...(...)If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.[/quote]Ah! Someone who wants to understand and not just complain....[/quote]An excellent explanation, John! Well done!You did overlook the last part of Raghavendra's question, so I'll take that. The answer is YES. If you change the OVER clause to (ORDER BY UserID), each row will get the sales value of the next user ordered by UserID, so Joe get 700, Baker 400, Charlie 800, Able 1000, XRay 50, and Easy gets the NUL in this case.[/quote]Thank you Hugo and John. (as always... it is worth reading your feedbacks :))Fri, 02 Nov 2012 07:06:02 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxNot sure what real world value this question would be as it does not keep the sales attached to the correct UserName and UserID. Seems like you just corrupted your output.Wed, 31 Oct 2012 13:32:26 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Dan Graveen (10/31/2012)[/b][hr]Of course I had to read up on LEAD before answering this question. MSDN states: "Notice that because there is no lead value available for the last row of each partition, the default of zero (0) is returned.". Needless to say I got this one wrong.. I'm still working in 2008, so I'll have to wait before I can try it out. :unsure:[/quote]Hi Dan,The text you quote from the Books Online page on MSDN is part of the explanation of Example A. In that example, the LEAD function is used as [code="sql"]LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota[/code]The third argument of the LEAD function is its default value. The description of that argument includes: "If a default value is not specified, NULL is returned". In the Books Online Example A, a default value of 0 was specified, so it is used. In the Question of the Day, no default value is specified, so NULL will be returned.Wed, 31 Oct 2012 06:39:55 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxOf course I had to read up on LEAD before answering this question. MSDN states: "Notice that because there is no lead value available for the last row of each partition, the default of zero (0) is returned.". Needless to say I got this one wrong.. I'm still working in 2008, so I'll have to wait before I can try it out. :unsure:Wed, 31 Oct 2012 06:31:46 GMTDan Graveenhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxGreat question and discussion. Thanks.Wed, 31 Oct 2012 06:21:23 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx@John ArnottThanks for the full explanation which certainly helped me understand why I could be said to have got it wrong, which I admit I could not grasp at first. I'm afraid I am in the misinterpreters camp - perhaps if the query had asked if any row contained the value 50 or some other phrasing.Wed, 31 Oct 2012 05:37:09 GMTcall.copsehttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Koen Verbeeck (10/30/2012)[/b][hr][quote][b]Konstantin Reu (10/30/2012)[/b][hr]I think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere :-DNext is> Sales value for User Easy does NOT appear in the resultsAccording to the task' explanation that sounds like "SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) [b]sales[/b] FROM #Users1 ORDER BY SALES"field sales for user "Easy" will have value = 100. It is obvious and I've got this result.I've got a question why do choice "[b]Sales value for User Easy does NOT appear in the results[/b]," considered as correct part of answer ?!I would like to get point back.....[/quote]The sales value for User Easy is 50. 50 is not returned in the resultset.Hence, the sales value for user easy does not appear in the results.[/quote]Please be carefull as I when I read explanation. Tasks' SQL statement contains expression through LEAD function.and synonym for it is named "sales". Because of it I insist thatsuggestion "Sales value for User Easy does NOT appear in the results"is false.Because as You can guess column "Sales" will have value for user Easy.And it doesn't matter what value will be in it, according to the task non null value is there ;)PS. I understand opinion all who isn't arguing. But I consider - task should be resolved in that manner as it is described.Wed, 31 Oct 2012 03:32:50 GMTKonstantin Reuhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Hugo Kornelis (10/30/2012)[/b][hr][quote][b]ben.norris (10/30/2012)[/b][hr]It doesn't state that the field is nullable[/quote]First: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.[/quote]>It does. The CREATE TABLE script does not include a NOT NULL >constraint, so the columns are all nullable.It isn't always true and you cannot be always sure about this behavior,because it depends on session setting SET ANSI_NULL_DFLT_ON|OFF.Wed, 31 Oct 2012 03:23:51 GMTKonstantin Reuhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]bitbucket-25253 (10/30/2012)[/b][hr][quote][b]Meow Now (10/30/2012)[/b][hr]One good thing about this question, I learned about a new feature in 2012. I missed the question, like most, because I made an assumption that the author made a typo. The correct answers do not really test one's knowledge of the functionality of SQL, merely his or her reading comprehension. It is kind of like that joke about a plane crashing on the U.S./Mexico border..what country do the survivors get buried in?/*edited for grammar yo*/[/quote]Always remember "making an assumption" means making an A$$ out of you and me.[/quote]bitbucket, actually that's not the case here:* Those who [i]made an assumption[/i] about the question scored a point.* Those who [b][i]correctly[/i][/b] answered [b]the question [u]as asked[/u][/b], didn't.Wed, 31 Oct 2012 01:24:18 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Meow Now (10/30/2012)[/b][hr]One good thing about this question, I learned about a new feature in 2012. I missed the question, like most, because I made an assumption that the author made a typo. The correct answers do not really test one's knowledge of the functionality of SQL, merely his or her reading comprehension. It is kind of like that joke about a plane crashing on the U.S./Mexico border..what country do the survivors get buried in?/*edited for grammar yo*/[/quote]Always remember "making an assumption" means making an A$$ out of you and me.Tue, 30 Oct 2012 17:40:18 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Lokesh Vij (10/30/2012)[/b][hr][quote][b]craig 81366 (10/30/2012)[/b][hr][quote][b]Lokesh Vij (10/30/2012)[/b][hr][code="plain"] USERID USERNAME SALES ------------------------------ 5 XRay NULL 6 Easy 100 1 Joe 400 3 Charlie 700 2 Baker 800 4 Able 1000 [/code][/quote]Given the results of the query [i]as stated in the question[/i], courtesy of Lokesh, "Easy [b]does[/b] have a Sales value"!Yes, an argument can be made to interpret the question as the author intended - but that's [b]not[/b] what the question asked!Hence the model answer is incorrect.[/quote]:-)I agree Craig. As already mentioned earlier, I did mis-interpret the answer choice.[/quote]+1 :ermm:This is a second one in a row that I got wrong. That has not happened to me in about two years.Thanks, anyway!Tue, 30 Oct 2012 15:24:58 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxOne good thing about this question, I learned about a new feature in 2012. I missed the question, like most, because I made an assumption that the author made a typo. The correct answers do not really test one's knowledge of the functionality of SQL, merely his or her reading comprehension. It is kind of like that joke about a plane crashing on the U.S./Mexico border..what country do the survivors get buried in?/*edited for grammar yo*/Tue, 30 Oct 2012 14:44:34 GMTMeow Nowhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]john.arnott (10/30/2012)[/b][hr]... Let's step through the query...[/quote]Thank you, john.arnott, for your detailed explanation. No doubt there were many of us who benefitted!Tue, 30 Oct 2012 12:57:52 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxgood question but the confusion is only in whether we have to answer to consider the current 'sales' column or after the query execution 'salesgoal' (which is not mentioned by mistake) column.Tue, 30 Oct 2012 12:49:28 GMTswarn_softhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote]The only confusion has been pointed out by john.arnott. The alias of the "LEAD (Sales,1) OVER (ORDER BY Sales)" should have been "SalesGoal", not "Sales".May be I am interpretting it wrongly, "the sales value for user EASY does not appear in the result" --> I thougt this means "sales value corresponding to user EASY should be NULL", which indeed is not the case :-)[/quote]Interpreted the question wrong myself ... very easy to read as user EASY doesn't have a result appear.Tue, 30 Oct 2012 12:15:58 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]craig 81366 (10/30/2012)[/b][hr][quote][b]Lokesh Vij (10/30/2012)[/b][hr][code="plain"] USERID USERNAME SALES ------------------------------ 5 XRay NULL 6 Easy 100 1 Joe 400 3 Charlie 700 2 Baker 800 4 Able 1000 [/code][/quote]Given the results of the query [i]as stated in the question[/i], courtesy of Lokesh, "Easy [b]does[/b] have a Sales value"!Yes, an argument can be made to interpret the question as the author intended - but that's [b]not[/b] what the question asked!Hence the model answer is incorrect.[/quote]:-)I agree Craig. As already mentioned earlier, I did mis-interpret the answer choice.Tue, 30 Oct 2012 11:44:23 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxGreat question. Missed it, but learned something. Also made me look forward to the upgrade to SQL 2012!Tue, 30 Oct 2012 11:08:23 GMTRob Schripsemahttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]john.arnott (10/30/2012)[/b][hr][quote][b]Raghavendra Mudugal (10/30/2012)[/b][hr]Just trying to understand...(...)If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.[/quote]Ah! Someone who wants to understand and not just complain....[/quote]An excellent explanation, John! Well done!You did overlook the last part of Raghavendra's question, so I'll take that. The answer is YES. If you change the OVER clause to (ORDER BY UserID), each row will get the sales value of the next user ordered by UserID, so Joe get 700, Baker 400, Charlie 800, Able 1000, XRay 50, and Easy gets the NUL in this case.Tue, 30 Oct 2012 10:30:30 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Raghavendra Mudugal (10/30/2012)[/b][hr][quote][b]vk-kirov (10/30/2012)[/b][hr]...The value 50.00 does not appear in the results, so I see no problem with the answer given....[/quote]Just trying to understand...As it says OVER (ORDER BY Sales) and EASY is on the low it will never pick the 50.00 of him as the OFFSET is been used as 1 so it has to go to the next available row in the dataset and it has to pick the value. If 50.00 was supposed to be displayed for EASY then does it not defeats the purpose of using the LEAD?If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.[/quote]Ah! Someone who wants to understand and not just complain.... Let's step through the query, and I'll admit that I may not be the best to do that since, as I said in the second comment posted, I don't have SQL 2012 installed. First, for ease of review, here's the CREATE TABLE:[code="sql"]CREATE TABLE #Users1(UserId INT IDENTITY,UserName VARCHAR(8),Sales Decimal(6,2))[/code]and its stated contents:[code="plain"]UserId UserName Sales 1 Joe 100.00 2 Baker 700.00 3 Charlie 400.00 4 Able 800.00 5 XRay 1000.00 6 Easy 50.00 [/code]and the query (reformatted a bit for readability):[code="sql"]SELECT UserId --1 ,UserName --2 ,LEAD (Sales,1) --3 OVER (ORDER BY Sales) --4 AS sales --5FROM #Users1 --6ORDER BY SALES --7[/code]The LEAD() function in line 3 above may take three immediate parameters, two of which are used here. The first says which column in which to look ahead (SALES), and the second says how far (1 row). The third parameter would say what to return as a default if no row is available for the look-ahead. Here it's left off, so is NULL. In addition to the three parameters, the LEAD() function also has other elements controlling its behaviour in its OVER clause, elements that looked very familiar to this SQL 2005 user from their similarity to the ranking functions Row_Number(), NTile(), Rank() and Dense_Rank(). The "Order by Sales" in line 4 above tells LEAD to put the rows in that order before reporting what's in the next row. This means that internally for this function, the table is looked at as having a logical order of[code="plain"]UserId UserName Sales 6 Easy 50.001 Joe 100.00 3 Charlie 400.002 Baker 700.004 Able 800.00 5 XRay 1000.00[/code]But of course, that's not what's returned as the query's result. For that, we get what's specified in the Select list -- UserID (line 1), UserName (line 2), and the output of the LEAD() function assigned the alias "sales" (line 5). For the first row considered in the order of the table's "Sales", we get UserID =6, UserName=Easy and the LEAD() function gives the next row's value of Sales, which is 100.00. Thus, the result for "Easy" is:[code="plain"]6 Easy 100.00[/code]Similarly, each results row is given its third column value in turn. The row at the end, based on the "OVER (Order By Sales)", is that of UserID=6, UserName=XRay. Since there's no following row, the LEAD() function gives that row the default of NULL. If the third parameter had been specified in line 3 of the query as, say[code="sql"]LEAD (Sales,1,5000)[/code]then it would have returned that specified default of 5000.Finally, the result set itself has its own ORDER BY in line 7. Recall that although an alias may not be used in the WHERE clause, it is certainly available for the ORDER BY of a query. So, the results are returned in the order of the alias "sales", that is, the result of the LEAD() function. I would expect the results to be as follows, with UserID 5 at the top since NULL would sort before the numerical values:[code="plain"]UserId UserName Sales 5 XRay NULL6 Easy 100.001 Joe 400.00 3 Charlie 700.002 Baker 800.004 Able 1000.00[/code]Tue, 30 Oct 2012 10:11:57 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]vk-kirov (10/30/2012)[/b][hr]...The value 50.00 does not appear in the results, so I see no problem with the answer given....[/quote]Just trying to understand...As it says OVER (ORDER BY Sales) and EASY is on the low it will never pick the 50.00 of him as the OFFSET is been used as 1 so it has to go to the next available row in the dataset and it has to pick the value. If 50.00 was supposed to be displayed for EASY then does it not defeats the purpose of using the LEAD?If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.Tue, 30 Oct 2012 09:01:22 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]ben.norris (10/30/2012)[/b][hr][quote]Aha, right you are, my mistake. Shame Hugo was too busy being condescending to make that point clear.[/quote]I am sorry to hear that you perceived my reply as condescending. That was never my intention.Tue, 30 Oct 2012 08:59:00 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Koen Verbeeck (10/30/2012)[/b][hr][quote][b]Konstantin Reu (10/30/2012)[/b][hr]I think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere :-DNext is> Sales value for User Easy does NOT appear in the resultsAccording to the task' explanation that sounds like "SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) [b]sales[/b] FROM #Users1 ORDER BY SALES"field sales for user "Easy" will have value = 100. It is obvious and I've got this result.I've got a question why do choice "[b]Sales value for User Easy does NOT appear in the results[/b]," considered as correct part of answer ?!I would like to get point back.....[/quote]The sales value for User Easy is 50. 50 is not returned in the resultset.Hence, the sales value for user easy does not appear in the results.[/quote]The column in the results which has been given the name "Sales" contains a value for user Easy. Hence the "Sales value for user Easy" DOES appear in the results.And by what process other than guesswork, does "the sales recorded for a completely different person" translate into "salesgoal" ?Tue, 30 Oct 2012 08:42:47 GMTarchie flockharthttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]craig 81366 (10/30/2012)[/b][hr][quote][b]ben.norris (10/30/2012)[/b][hr][quote][b]Hugo Kornelis (10/30/2012)[/b][hr][quote][b]ben.norris (10/30/2012)[/b][hr]It doesn't state that the field is nullable[/quote]First: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.[/quote]According to the docs LEAD returns the default value which only happens to be NULL for nullable columns. So yes it does change the answer. A poor question to rely on assumption for something you are specifically looking for in the answer.[/quote][i]default[/i] is the optional 3rd argument to the LEAD function. This is described as: "The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. ..."In the question, no default is specified, hence even if SALES were non nullable, the LEAD function would return NULL.[/quote]Aha, right you are, my mistake. Shame Hugo was too busy being condescending to make that point clear.Tue, 30 Oct 2012 06:31:13 GMTben.norrishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]ben.norris (10/30/2012)[/b][hr][quote][b]Hugo Kornelis (10/30/2012)[/b][hr][quote][b]ben.norris (10/30/2012)[/b][hr]It doesn't state that the field is nullable[/quote]First: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.[/quote]According to the docs LEAD returns the default value which only happens to be NULL for nullable columns. So yes it does change the answer. A poor question to rely on assumption for something you are specifically looking for in the answer.[/quote][i]default[/i] is the optional 3rd argument to the LEAD function. This is described as: "The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. ..."In the question, no default is specified, hence even if SALES were non nullable, the LEAD function would return NULL.Tue, 30 Oct 2012 06:24:03 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Hugo Kornelis (10/30/2012)[/b][hr][quote][b]ben.norris (10/30/2012)[/b][hr]It doesn't state that the field is nullable[/quote]First: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.[/quote]According to the docs LEAD returns the default value which only happens to be NULL for nullable columns. So yes it does change the answer. A poor question to rely on assumption for something you are specifically looking for in the answer.Tue, 30 Oct 2012 06:13:31 GMTben.norrishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Konstantin Reu (10/30/2012)[/b][hr]I think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere :-DNext is> Sales value for User Easy does NOT appear in the resultsAccording to the task' explanation that sounds like "SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) [b]sales[/b] FROM #Users1 ORDER BY SALES"field sales for user "Easy" will have value = 100. It is obvious and I've got this result.I've got a question why do choice "[b]Sales value for User Easy does NOT appear in the results[/b]," considered as correct part of answer ?!I would like to get point back.....[/quote]The sales value for User Easy is 50. 50 is not returned in the resultset.Hence, the sales value for user easy does not appear in the results.Tue, 30 Oct 2012 06:09:34 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]ben.norris (10/30/2012)[/b][hr]It doesn't state that the field is nullable[/quote]First: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.Tue, 30 Oct 2012 06:04:03 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxI think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere :-DNext is> Sales value for User Easy does NOT appear in the resultsAccording to the task' explanation that sounds like "SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) [b]sales[/b] FROM #Users1 ORDER BY SALES"field sales for user "Easy" will have value = 100. It is obvious and I've got this result.I've got a question why do choice "[b]Sales value for User Easy does NOT appear in the results[/b]," considered as correct part of answer ?!I would like to get point back.....Tue, 30 Oct 2012 05:56:23 GMTKonstantin Reuhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]L' Eomot Inversé (10/30/2012)[/b][hr]Nice to see a question about a new feature in sql 2012.I took a guess as to what the code was intended to be, and got it right. But there are ambiguities here which we have to resolve by pure guesswork, and maybe the low success rate is the result of this. The current success rate is 23%, and the expected result of chosing one at random from each mutually contradictory pair is 25%, which indicates either no knowledge at all in the respondents (which I don't believe) or enough ambiguity in the question to have much the same effect as if answers had been random.[/quote]It would be interesting to find out what percentage got the answer right according the [i]stated[/i] question.Curently 57% selected option 2, but it's not clear how many of those also selected option 3. The upper bound could be calculated as the percentage that selected option 3 - the percentage that got the answer "offcially correct". I.e. 58 - 23 = 35%.Of course there are those who figured out the intent of the question and answered "correctly". So it's quite possible that as many as 50% did undertsand the issues the question was supposed to test.Tue, 30 Oct 2012 05:38:03 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxAn interesting question spoilt by the 3 points of ambiguity.Salesgoal mistakeThe Sales value for easy becomes 100, it is xray that is missing.It doesn't state that the field is nullableTue, 30 Oct 2012 05:22:44 GMTben.norrishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]Lokesh Vij (10/30/2012)[/b][hr][code="plain"] USERID USERNAME SALES ------------------------------ 5 XRay NULL 6 Easy 100 1 Joe 400 3 Charlie 700 2 Baker 800 4 Able 1000 [/code][/quote]Given the results of the query [i]as stated in the question[/i], courtesy of Lokesh, "Easy [b]does[/b] have a Sales value"!Yes, an argument can be made to interpret the question as the author intended - but that's [b]not[/b] what the question asked!Hence the model answer is incorrect.Tue, 30 Oct 2012 05:13:26 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxIt's great to see a question about new SQL 2012 functionality. And the question itself is quite good - but it could have been better.I saw two possible improvements beefore coming here:* Mentioning the version SQL Server 2012 in the question, to avoid possible confusion. (As a guideline, I try to remember mentioning the version when the functionality is not the same in all versions currenntly in mainstream support).* The confusion with the column alias name. We had to guess what the "sales target" was. I assume that the intention was to use this as the alias for the column computed with the LEAD function, but the actual alias was different.And after reading the discussion so far, I would add:* The description "Sales value for User Easy" was apparently too cryptic for some. I interpreted it right, but I can now understand that people would understand this differently. And it could indeed have been much easier - why not phrase the options as "The number 50.00 does NOT appear in the results" / "The number 50.00 does appear in the results"?* As Tom points out, the description could have been more than just a BOL quote."The value 50.00 is the lowest in the Sales column. That means that there is no row for which this value is the "next" when ordering by ascending sales, so the LEAD function will not return it.""For the row with the highest value in the Sales column, there is no "next" value in the Sales column when ordering by ascending sales. This is indeicated by the LEAD function returning NULL."Tue, 30 Oct 2012 04:21:36 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspxNice to see a question about a new feature in sql 2012.Would have been a good question except for the ambiguities introduced by hving the result column that should have been labelled salesgoal labelled sales, and asking about a sales value in the result when we couldn't tell whether the mistake in the code was just one column label, or a missing column. As it is, I'm not so sure.I took a guess as to what the code was intended to be, and got it right. But there are ambiguities here which we have to resolve by pure guesswork, and maybe the low success rate is the result of this. The current success rate is 23%, and the expected result of chosing one at random from each mutually contradictory pair is 25%, which indicates either no knowledge at all in the respondents (which I don't believe) or enough ambiguity in the question to have much the same effect as if answers had been random.edit: the explanation is a bit substandard too; it's correct (it's a direct quotation from BoL) but there's no comparison going on here so it's irrelevant to this question. A quotation from further on down the BoL page (where it talks about NULL showing up when the third parameter of LEAD is omitted) might have been more relevant, and less misleading - the reference to comparing with next row does somewhat suggest that the sales column and salesgoal column should both be present, maybe that's why only 40% got that half of the question right.Tue, 30 Oct 2012 04:04:07 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1378552-1222-1.aspx[quote][b]demonfox (10/30/2012)[/b][hr]oh , I got it wrong , I thought instead of null, it will give default 0 as output....learned something though ;-)thanks for the question...[/quote]+1I also thought it would default to zero based on the BOL example, and also thought that "EASY" would appear which it did, but the "sales value" did not, my bad interpretation.Learned something though, and that is worth infinitely more than the lost point which ultimately is worthless anyway :-PThanks, good question.Tue, 30 Oct 2012 04:03:23 GMTDugyC