November 2, 2012 at 7:06 am
Hugo Kornelis (10/30/2012)
john.arnott (10/30/2012)
Raghavendra Mudugal (10/30/2012)
Just trying to understand...(...)
If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.
Ah! Someone who wants to understand and not just complain....
An excellent explanation, John! Well done!
You did overlook the last part of Raghavendra's question, so I'll take that. The answer is YES. If you change the OVER clause to (ORDER BY UserID), each row will get the sales value of the next user ordered by UserID, so Joe get 700, Baker 400, Charlie 800, Able 1000, XRay 50, and Easy gets the NUL in this case.
Thank you Hugo and John. (as always... it is worth reading your feedbacks :))
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
November 2, 2012 at 3:02 pm
jlennartz (10/31/2012)
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.
You're absolutely correct that as is, the query would likely be sadly lacking in a real-world application. However, as I see it, the point was to distill the behavior of the LEAD() function, challenging the reader to consider how this new (in SQL 2012) feature works. I learned about a new tool in the chest here and, as I have said before in QOD discussions, was lookingfor a learning opportunity that goes beyond taking a quick look and stabbing at whatever answer feels right.
May I suggest that you give yourself such an opportunity and construct a "real world" example of LEAD(). Spend a bit of time on it and consider publishing it as a new QOD or, perhaps better yet, a short article that would help other MS-SQL users understand this new function.
November 3, 2012 at 2:54 am
Nice explanation John...
I dont have SQL Server 2012 but learned a new intresting thing.. 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 3, 2012 at 3:00 am
There is one more link if anyone want to read about LEAD :
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 5, 2012 at 11:31 am
john.arnott (11/2/2012)
jlennartz (10/31/2012)
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.You're absolutely correct that as is, the query would likely be sadly lacking in a real-world application. However, as I see it, the point was to distill the behavior of the LEAD() function, challenging the reader to consider how this new (in SQL 2012) feature works. I learned about a new tool in the chest here and, as I have said before in QOD discussions, was lookingfor a learning opportunity that goes beyond taking a quick look and stabbing at whatever answer feels right.
May I suggest that you give yourself such an opportunity and construct a "real world" example of LEAD(). Spend a bit of time on it and consider publishing it as a new QOD or, perhaps better yet, a short article that would help other MS-SQL users understand this new function.
You can usually learn something fro QOD, more times than not in the Discussions. That is why I follow QOD. And Thanks, John, for the suggestion, when we get SQL2012 I will consider doing that.
November 16, 2012 at 12:49 pm
Our software manager indicated I could install SQL Server 2012 Express and be able to test out the new functions.
Anybody have experience with that? I tried Express now I'm trying Express LocalDB and can seem to get the functions to operate.
Any hints?
Stephanie
November 16, 2012 at 5:04 pm
sdolan 41249 (11/16/2012)
Our software manager indicated I could install SQL Server 2012 Express and be able to test out the new functions.Anybody have experience with that? I tried Express now I'm trying Express LocalDB and can seem to get the functions to operate.
Any hints?
Stephanie
Hi, Stephanie, and welcome to SSC. Your best shot at getting help with that would be to add a new topic in an appropriate forum. If you click the "Forums" link on the left side of the page you're looking at now, you'll see a good sized list from which to choose. I'd think you'd be best off now in the "SQL 2012 - General" forum (http://www.sqlservercentral.com/Forums/Forum2799-1.aspx), under "SQL Server 2012".
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply