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