September 17, 2011 at 1:52 pm
Comments posted to this topic are about the item SubQuery
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 17, 2011 at 1:55 pm
Got it wrong.
I thought that there would be an error because the StudentId column has the alias "StudentNumber" inside the subquery and in line 25 it's being declared as StudentId again.
Interesting behaviour. Learned something new. 🙂
Thank you for the question.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 17, 2011 at 2:01 pm
Happy to report that I am first to get this right.
I don't enjoy this sort of question though - I am a human, not a T-SQL parser.
The question could have made the same point, with the same learning value, much more simply.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 17, 2011 at 4:21 pm
SQL Kiwi (9/17/2011)
Happy to report that I am first to get this right.I don't enjoy this sort of question though - I am a human, not a T-SQL parser.
The question could have made the same point, with the same learning value, much more simply.
It was due to that human nature (it is very easy for people to make a mistake in column aliasing as shown here) I wanted to demonstrate the question like this. As you said though, I could have given a much simpler example. This just happened to be one that I was working on as an answer to a forum question at the time of submission.
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 17, 2011 at 4:37 pm
SQLRNNR (9/17/2011)
...This just happened to be one that I was working on as an answer to a forum question at the time of submission.
I see. Thanks for the explanation.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 18, 2011 at 4:29 am
Indeed, it's an interesting thing to learn. Thanks.
September 18, 2011 at 11:19 am
Sudhir Dwivedi (9/18/2011)
Indeed, it's an interesting thing to learn. 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 12:03 am
Nice question.
I chose the third (wrong) answer because I didn't notice the DATE data type and thought it was DATETIME 🙂
September 19, 2011 at 12:32 am
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
September 19, 2011 at 1:28 am
DOH! Right answer, for the wrong reason.
I completely missed the aliases and selected the correct answer on the DATE type.
So I learnt something and won a checky point.
Making this my favorite QotD so far this week 🙂
September 19, 2011 at 1:40 am
This was removed by the editor as SPAM
September 19, 2011 at 2:37 am
Nice question ... interesting to learn something new too.
September 19, 2011 at 2:46 am
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 😉
September 19, 2011 at 5:42 am
Tough question with the amount of T-SQL to review, but I did get it correct. I think it was more luck than anything.
http://brittcluff.blogspot.com/
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply