LEAD - 1
-
Comments posted to this topic are about the item LEAD - 1
-
Hey Ron, There seems to be some confustion with answer choices here. When we have ORDER BY caluse on Sales, it will order the results in the following fashion:
UserId UserName Sales----------- -------- ---------------------------------------
6 Easy 50.00
1 Joe 100.00
3 Charlie 400.00
2 Baker 700.00
4 Able 800.00
5 XRay 1000.00
Now when we apply LEAD function over this, XRay will have NULL value not EASY as mentioned in the ansewer choices.
Please verify!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks for the intro to a new SQL 2012 function (Making me itch for our app to move on from 2005!). I love the idea of this new function -- much simpler than isolating a Row_Number() in a CTE or doing self-joins.
As Lokesh implies, there may have been some ambiguity in the way this was constructed, or maybe just a disconnect between the SQL code and the stated question. I got my answer by researching BOL on the web (had to -- see above), but assumed that where the question said "sales goal", it meant the LEAD result given an alias "sales" in the SQL. I tlooks as though the LEAD function is ordered by the Sales column in the temp table, whereas the results are ordered by the alias "Sales" assigned to the output of LEAD. My responses were scored as "correct", so now I'm wondering what really happens.
Lokesh, did you run the code with the LEAD function?
Ron, can you confirm?
-
Hi John,
Here is the output what I am getting:
USERID USERNAME SALES------------------------------
5 XRay NULL
6 Easy 100
1 Joe 400
3 Charlie 700
2 Baker 800
4 Able 1000
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks for question about the new feature of MSSQL 2012!
Lokesh Vij (10/29/2012)
Hey Ron, There seems to be some confustion with answer choices here. When we have ORDER BY caluse on Sales, it will order the results in the following fashion:UserId UserName Sales----------- -------- ---------------------------------------
6 Easy 50.00
1 Joe 100.00
3 Charlie 400.00
2 Baker 700.00
4 Able 800.00
5 XRay 1000.00
Now when we apply LEAD function over this, XRay will have NULL value not EASY as mentioned in the ansewer choices.
Please verify!
The "Sales value for User Easy" mentioned in the first two options means "the result of the query SELECT Sales FROM #Users1 WHERE UserName = 'Easy' ". Therefore, the "Sales value for User Easy" is equal to 50.00.
Below is the result of the QOTD query:
UserId UserName SalesGoal----------- -------- ---------------------------------------
6 Easy 100.00
1 Joe 400.00
3 Charlie 700.00
2 Baker 800.00
4 Able 1000.00
5 XRay NULL
The value 50.00 does not appear in the results, so I see no problem with the answer given.
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".
-
vk-kirov (10/30/2012)
Thanks for question about the new feature of MSSQL 2012!Lokesh Vij (10/29/2012)
Hey Ron, There seems to be some confustion with answer choices here. When we have ORDER BY caluse on Sales, it will order the results in the following fashion:UserId UserName Sales----------- -------- ---------------------------------------
6 Easy 50.00
1 Joe 100.00
3 Charlie 400.00
2 Baker 700.00
4 Able 800.00
5 XRay 1000.00
Now when we apply LEAD function over this, XRay will have NULL value not EASY as mentioned in the ansewer choices.
Please verify!
The "Sales value for User Easy" mentioned in the first two options means "the result of the query SELECT Sales FROM #Users1 WHERE UserName = 'Easy' ". Therefore, the "Sales value for User Easy" is equal to 50.00.
Below is the result of the QOTD query:
UserId UserName SalesGoal----------- -------- ---------------------------------------
6 Easy 100.00
1 Joe 400.00
3 Charlie 700.00
2 Baker 800.00
4 Able 1000.00
5 XRay NULL
The value 50.00 does not appear in the results, so I see no problem with the answer given.
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 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
oh , I got it wrong , I thought instead of null, it will give default 0 as output..
..
learned something though 😉
thanks for the question...
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
This was removed by the editor as SPAM
-
Great question! Always good to have some 2012 questions.
For people who want to learn more about the subject, this is an amazing book:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Thanks Ron.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
vk-kirov (10/30/2012)
Thanks for question about the new feature of MSSQL 2012!++1
😀
-
I thought the answers were a trifle ambiguous--I think it would have been a lot clearer if the 3rd column was aliased as "Sales Goal" rather than "Sales". As it is, I got it wrong because I wasn't sure which way round the Easy sales answer should be!
-
demonfox (10/30/2012)
oh , I got it wrong , I thought instead of null, it will give default 0 as output....
learned something though 😉
thanks for the question...
+1
I 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 😛
Thanks, good question.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font] -
Nice 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.
Tom
-
It'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."
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply