LEAD - 1

Question

LEAD - 1

Viewing 15 posts - 31 through 45 (of 51 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

craig 81366 (10/30/2012)
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.

🙂

I agree Craig. As already mentioned earlier, I did mis-interpret the answer choice.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

mickyT SSChampion Points: 10360 More actions · Answer 2

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 🙂

Interpreted the question wrong myself ... very easy to read as user EASY doesn't have a result appear.

swarn_soft Old Hand Points: 351 More actions · Answer 3

good 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.

Rose Bud Hall of Fame Points: 3147 More actions · Answer 4

john.arnott (10/30/2012)
... Let's step through the query...

Thank you, john.arnott, for your detailed explanation. No doubt there were many of us who benefitted!

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 5

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*/

Aigle de Guerre!

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Lokesh Vij (10/30/2012)
craig 81366 (10/30/2012)
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.
🙂
I agree Craig. As already mentioned earlier, I did mis-interpret the answer choice.

+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!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Meow Now (10/30/2012)
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*/

Always remember "making an assumption" means making an A$$ out of you and me.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

craig 81366 SSC Eights! Points: 808 More actions · Answer 8

bitbucket-25253 (10/30/2012)
Meow Now (10/30/2012)
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*/
Always remember "making an assumption" means making an A$$ out of you and me.

bitbucket, actually that's not the case here:

* Those who made an assumption about the question scored a point.

* Those who correctly answered the question as asked, didn't.

Konstantin Reu Hall of Fame Points: 3149 More actions · Answer 9

Hugo Kornelis (10/30/2012)
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.

>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.

MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2

Konstantin Reu Hall of Fame Points: 3149 More actions · Answer 10

Koen Verbeeck (10/30/2012)
Konstantin Reu (10/30/2012)
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 😀
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.....
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.

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 that

suggestion

"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.

MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2

call.copse SSCoach Points: 17960 More actions · Answer 11

@john-2 Arnott

Thanks 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.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 12

(Bob Brown)

SSCrazy

Points: 2705

October 31, 2012 at 6:21 am

#1554568

Great question and discussion. Thanks.

Dan Graveen Mr or Mrs. 500 Points: 525 More actions · Answer 13

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:

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 14

Dan Graveen (10/31/2012)
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:

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 LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota

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.

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/

jlennartz SSCommitted Points: 1574 More actions · Answer 15

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.