Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

LEAD - 1 Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 1:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 2,509, Visits: 2,386
vk-kirov (10/30/2012)
Thanks for question about the new feature of MSSQL 2012!


++1
Post #1378600
Posted Tuesday, October 30, 2012 2:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 1,632, Visits: 5,587
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!
Post #1378612
Posted Tuesday, October 30, 2012 4:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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

Thanks, good question.


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1378647
Posted Tuesday, October 30, 2012 4:04 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 8,743, Visits: 9,292
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
Post #1378648
Posted Tuesday, October 30, 2012 4:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1378660
Posted Tuesday, October 30, 2012 5:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:32 AM
Points: 45, Visits: 338
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.
Post #1378695
Posted Tuesday, October 30, 2012 5:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 6:53 AM
Points: 22, Visits: 80
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
Post #1378696
Posted Tuesday, October 30, 2012 5:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:32 AM
Points: 45, Visits: 338
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.
Post #1378702
Posted Tuesday, October 30, 2012 5:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 21, 2014 11:36 AM
Points: 2,083, Visits: 521
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.....


MCITP x 3
MCSE x 1
Post #1378707
Posted Tuesday, October 30, 2012 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1378715
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse