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

Order By Clause Expand / Collapse
Author
Message
Posted Wednesday, September 7, 2011 11:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 1,117, Visits: 1,231
Thanks for the question!

-Dan



Post #1171285
Posted Wednesday, September 7, 2011 4:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 8,571, Visits: 9,076
Good question.

I got it wrong. Q1 is obviously wroing, and I immediately concluded it was all about "order by" and just skim the rest for "order by" related errors - so missed the obvious problem with 4. Another confirmation that one should read code carefully, not skim it carelessly.


Tom
Post #1171440
Posted Wednesday, September 7, 2011 4:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 8,571, Visits: 9,076
But of course one could say that the query processing ought to notice that there is no ambiguity in number 4, because any possible ambiguity is eliminated by the ON clause of the join. Would such ambiguity elimination be a good thing or a bad thing? I'm in two minds about it.

Tom
Post #1171443
Posted Wednesday, September 7, 2011 9:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Tom.Thomson (9/7/2011)
But of course one could say that the query processing ought to notice that there is no ambiguity in number 4, because any possible ambiguity is eliminated by the ON clause of the join. Would such ambiguity elimination be a good thing or a bad thing? I'm in two minds about it.
Because the two values are equal, I agree that it's not technical ambiguity; however, you can use nonequalities in the joins too like:

ON Column1 > Column2

Shouldn't happen often but the complexities in the query optimizer would be too difficult for Microsoft to handle

And to make it clear, it was due to the ORDER BY clause so the subject of the QOTD was still okay. If you were to remove the ORDER BY clauses, all 4 queries would run properly. Query 1 and 4 fail because of the invalid ORDER BY clauses -- not because of any other reason.
Post #1171499
Posted Wednesday, September 7, 2011 10:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 7:06 AM
Points: 1,216, Visits: 308
Thanks for the nice questions!!
Post #1171515
Posted Thursday, September 8, 2011 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:26 AM
Points: 6, Visits: 43
I agree.. Never would have put the Order by in the sub because I don't see the reason too. So I would have never learn it would fail until now
Post #1171769
Posted Tuesday, September 13, 2011 5:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Really had to think through this one. Thanks for a tough question of the day.

http://brittcluff.blogspot.com/
Post #1174010
Posted Tuesday, November 1, 2011 1:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 1, 2011 1:53 AM
Points: 13, Visits: 14
3rd will also fail since ambiguous column with order by.
Post #1198476
Posted Thursday, May 31, 2012 2:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:06 AM
Points: 447, Visits: 133
Query 3 would fail because both tables contain employee id column but, alias name of employee id have not been used in order by clause. Please check for the Query 3 and revert back.
Post #1308794
Posted Sunday, June 3, 2012 7:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Nandhini Ramanujam (5/31/2012)
Query 3 would fail because both tables contain employee id column but, alias name of employee id have not been used in order by clause. Please check for the Query 3 and revert back.


Query 3 does not fail. if there is more than one EmployeeID column, the ORDER BY clause uses the one that is in the select statement. There is only one in the select statement so the query executes just fine.
Post #1310213
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse