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 ««1234»»»

SubQuery Expand / Collapse
Author
Message
Posted Monday, September 19, 2011 1:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 3,905, Visits: 5,078
Thanks for the question, Jason
I actually quite enjoyed analysing this from all angles.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1177099
Posted Monday, September 19, 2011 2:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 1:55 AM
Points: 298, Visits: 236
Nice question ... interesting to learn something new too.
Post #1177110
Posted Monday, September 19, 2011 2:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 1,736, Visits: 6,326
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
Post #1177113
Posted Monday, September 19, 2011 5:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
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/
Post #1177192
Posted Monday, September 19, 2011 5:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 1,803, Visits: 2,168
Thanks. Got it right - but almost went for the first instance of the name change. Good thing I kept "parsing". Thanks for the lesson!

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1177193
Posted Monday, September 19, 2011 6:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 12:40 AM
Points: 1,314, Visits: 476
Nice question! Thanks!
Post #1177219
Posted Monday, September 19, 2011 8:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 21,619, Visits: 15,275
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1177278
Posted Monday, September 19, 2011 8:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 21,619, Visits: 15,275
stewartc-708166 (9/19/2011)
Thanks for the question, Jason
I 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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1177279
Posted Monday, September 19, 2011 8:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 21,619, Visits: 15,275
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1177280
Posted Monday, September 19, 2011 8:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 21,619, Visits: 15,275
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




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1177281
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse