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 @ 12:19 PM
Points: 3,694, Visits: 4,828
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 03, 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: Yesterday @ 7:53 AM
Points: 1,659, Visits: 6,008
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: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
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: Monday, April 21, 2014 8:44 AM
Points: 1,633, Visits: 2,038
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: Today @ 1:42 AM
Points: 1,238, Visits: 449
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: Today @ 2:38 PM
Points: 20,485, Visits: 14,142
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: Today @ 2:38 PM
Points: 20,485, Visits: 14,142
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: Today @ 2:38 PM
Points: 20,485, Visits: 14,142
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: Today @ 2:38 PM
Points: 20,485, Visits: 14,142
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