Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LEAD - 1


LEAD - 1

Author
Message
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3602 Visits: 3235
vk-kirov (10/30/2012)
Thanks for question about the new feature of MSSQL 2012!


++1
:-D
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6213
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!
DugyC
DugyC
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 Visits: 779
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 :-P

Thanks, good question.

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12001
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

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11557
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
craig 81366
craig 81366
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 486
Lokesh Vij (10/30/2012)


  USERID   USERNAME    SALES  
------------------------------
5 XRay NULL
6 Easy 100
1 Joe 400
3 Charlie 700
2 Baker 800
4 Able 1000


Given the results of the query as stated in the question, courtesy of Lokesh, "Easy does have a Sales value"!
Yes, an argument can be made to interpret the question as the author intended - but that's not what the question asked!
Hence the model answer is incorrect.
ben.norris
ben.norris
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 97
An interesting question spoilt by the 3 points of ambiguity.

Salesgoal mistake
The Sales value for easy becomes 100, it is xray that is missing.
It doesn't state that the field is nullable
craig 81366
craig 81366
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 486
L' Eomot Inversé (10/30/2012)
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.


It would be interesting to find out what percentage got the answer right according the stated 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.
Konstantin Reu
Konstantin Reu
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 752
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 :-D

Next is
> Sales value for User Easy does NOT appear in the results
According to the task' explanation that sounds like
"SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) sales 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 "Sales value for User Easy does NOT appear in the results," considered as correct part of answer ?!

I would like to get point back.....

MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11557
ben.norris (10/30/2012)
It doesn't state that the field is nullable

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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search