September 19, 2011 at 6:43 am
Nice question! Thanks!
September 19, 2011 at 8:45 am
tommyh (9/19/2011)
JOIN (SELECT StudentId as StudentNumber, max(RequestNbr) as RequestNb
FROM Request
...
GROUP BY StudentId) as Latest (StudentId, RequestNbr)
ON Request.StudentId = Latest.StudentId
AND Request.RequestNbr = Latest.RequestNbr
Just to clearify. The whole point was to point out that the bold parts dont match and that SQL instead is using the italic names in the join?
If above is true then read on... otherwise ignore:
I got the answer right for all the wrong reasons. I simply didnt see the change in the names. And it took one heck off a time to see the change in the names. When i first read the question i was so sure it would be something odd about the DATE datatype (since one answer had timeportion in it) or something with the MAX in the subquery. So i focused on that but couldnt find anything odd about it. So that left me with only one answer. Which was the right answer.
PS Im not being sarcastic or anything. I for one learned something... if my assumption is correct. Its just that in this case i feel like the point was drowned in code that wasnt relevant. Though atleast it made me think ALOT 😀
/T
yes - pretty much the whole point. And yes - it was a lot of code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 8:45 am
stewartc-708166 (9/19/2011)
Thanks for the question, JasonI actually quite enjoyed analysing this from all angles.
Thanks Stewart - glad you liked it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 8:46 am
Toreador (9/19/2011)
Got it right, even though the correct answer isn't listed. But I guessed rightly that the author assumed we all use US date formats 😉
I should have included that set option in the query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 8:47 am
Thomas Abraham (9/19/2011)
Thanks. Got it right - but almost went for the first instance of the name change. Good thing I kept "parsing". Thanks for the lesson!
Glad you got it right and learned:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 8:47 am
palotaiarpad (9/19/2011)
Nice question! Thanks!
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 10:39 am
Got it right, but it took me twenty minutes of head scratching.
Thanks!
September 19, 2011 at 2:55 pm
Thanks for the question. I got it right. I haven't used this functionality yet so I was guessing a little that this is how it works.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
September 19, 2011 at 4:37 pm
I, like some others, got the question right for the wrong reason. I focused on the date declaration in the parameters and only caught the renaming of the sub-query columns after I had chosen the correct answer. Interesting behavior in regards to aliasing this way. Thanks for the question, I learned something new today!
September 19, 2011 at 4:41 pm
Kenneth Wymore (9/19/2011)
I, like some others, got the question right for the wrong reason. I focused on the date declaration in the parameters and only caught the renaming of the sub-query columns after I had chosen the correct answer. Interesting behavior in regards to aliasing this way. Thanks for the question, I learned something new today!
Good to hear and glad you learned.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 4:41 pm
Trey Staker (9/19/2011)
Thanks for the question. I got it right. I haven't used this functionality yet so I was guessing a little that this is how it works.
Good guess;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 4:44 pm
Any idea as to why they would allow this behavior in a join? Seems to me like it would actually introduce more confusion than convenience.
September 19, 2011 at 4:50 pm
Kenneth Wymore (9/19/2011)
Any idea as to why they would allow this behavior in a join? Seems to me like it would actually introduce more confusion than convenience.
I think they have to allow it due to the requirement that a subquery that is based on values instead of a query requires the same syntax.
Here's an article on that. http://jasonbrimhall.info/2011/08/31/bitwise-and-derived-table-revisited/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 6:34 pm
Ah, Thanks Jason. I'll check that out.
September 20, 2011 at 5:22 am
Nice question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply