InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 12:29:04 GMT20http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxvery nicw question..i got it wrong though:-DThu, 24 Jan 2013 21:30:29 GMTkalyani.k478http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]Nandhini Ramanujam (5/31/2012)[/b][hr]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.[/quote]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.Sun, 03 Jun 2012 07:12:08 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxQuery 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.Thu, 31 May 2012 02:13:24 GMTNandhini Ramanujamhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx3rd will also fail since ambiguous column with order by.Tue, 01 Nov 2011 01:55:06 GMTjitesh-430609http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxReally had to think through this one. Thanks for a tough question of the day.Tue, 13 Sep 2011 05:22:54 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxI 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 nowThu, 08 Sep 2011 08:25:05 GMTdavidbheweshttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxThanks for the nice questions!!Wed, 07 Sep 2011 22:38:43 GMTCoolCodeSharehttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]Tom.Thomson (9/7/2011)[/b][hr]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.[/quote]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 > Column2Shouldn't happen often but the complexities in the query optimizer would be too difficult for Microsoft to handle :-DAnd 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.Wed, 07 Sep 2011 21:13:20 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxBut 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.Wed, 07 Sep 2011 16:23:23 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxGood 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.Wed, 07 Sep 2011 16:13:25 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxThanks for the question! -DanWed, 07 Sep 2011 11:20:59 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxThanks for the excellent question, I really had to think.#4 was obvious due to the SELECT *, but It took a while before I realized that you need a TOP clause in the sub select. BTW, SELECT * is one of the top mistakes that you should avoid, and this is one of the reasons, but there are plenty of more.Wed, 07 Sep 2011 02:14:14 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]cengland0 (9/6/2011)[/b][hr][quote][b]Tee Time (9/6/2011)[/b][hr]The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.[/quote]I selected that option, as I didn't realise that ordering by an unqualified column would use the one specified in the Select.I found the question difficult as a lesson I learned many years ago is always to qualify everything, both for clarity, and to prevent unexpected results.Wed, 07 Sep 2011 01:10:18 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]antony-688446 (9/6/2011)[/b][hr]Ok, own question answered - compatibility level was set to SQL Server 2000 (80) (Don't ask me why, as I don't know...). Changed to 2005, and the query now executes as per BOL.Nice to finally get an answer!And great question, as I've finally found something that compatibility levels affect...[/quote]I was about to answer with a test setup that I created.[code="sql"]IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tab1' AND type = 'u') BEGIN DROP TABLE Tab1 ENDGOIF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tab2' AND type = 'u') BEGIN DROP TABLE Tab2 ENDGOSELECT TOP 10000 OrderID = IDENTITY(INT,1,1), OrderAmt = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY), OrderDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME) INTO dbo.tab1 FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 go CREATE TABLE [dbo].tab2([OrderID] [int] NOT NULL,[OrderDetailID] [int] NOT NULL,[PartAmt] [money] NULL,[PartID] [int] NULL);Insert Into tab2 (OrderID,OrderDetailID,PartAmt,PartID) Select OrderID, OrderDetailID = 1, PartAmt = OrderAmt / 2, PartID = ABS(CHECKSUM(NEWID()))%1000+1 FROM tab1 SELECT a.OrderID FROM dbo.tab1 a INNER JOIN dbo.tab2 b ON a.OrderID = b.OrderIDORDER BY OrderIDDROP TABLE tab1DROP TABLE tab2[/code]SQL 2005 automatically uses the column in the select for the order by in the case we specify just this one column.I had been caught by the ambiguous column several times - but compat mode must have been at play there.Tue, 06 Sep 2011 16:31:37 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxOk, own question answered - compatibility level was set to SQL Server 2000 (80) (Don't ask me why, as I don't know...). Changed to 2005, and the query now executes as per BOL.Nice to finally get an answer!And great question, as I've finally found something that compatibility levels affect...Tue, 06 Sep 2011 16:20:25 GMTantony-688446http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]SQLRNNR (9/6/2011)[/b][hr][quote][b]cengland0 (9/6/2011)[/b][hr][quote][b]antony-688446 (9/6/2011)[/b][hr]Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives? The following code[code="sql"]SELECT a.co_seqFROM F_Pm_Tenant aJOIN F_Contact b ON a.co_seq = b.co_seqORDER BY co_seq[/code]gets the result:Msg 209, Level 16, State 1, Line 4Ambiguous column name 'co_seq'.every time. What is the difference (apart from the table & column names!) between this and the example in the question?Confused :unsure:[/quote]Which version of SQL Server are you using? I tested this on 2005.[/quote]3 should fail as well for the very reason stated - it is ambiguous.[/quote]Which would mean that the question and BOL are wrong...Tue, 06 Sep 2011 16:08:22 GMTantony-688446http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]cengland0 (9/6/2011)[/b][hr][quote][b]antony-688446 (9/6/2011)[/b][hr]Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives? The following code[code="sql"]SELECT a.co_seqFROM F_Pm_Tenant aJOIN F_Contact b ON a.co_seq = b.co_seqORDER BY co_seq[/code]gets the result:Msg 209, Level 16, State 1, Line 4Ambiguous column name 'co_seq'.every time. What is the difference (apart from the table & column names!) between this and the example in the question?Confused :unsure:[/quote]Which version of SQL Server are you using? I tested this on 2005.[/quote]3 should fail as well for the very reason stated - it is ambiguous.Tue, 06 Sep 2011 16:05:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxSQL Server 2005 SP3 - 9.00.4060 (which I can't find on MS's version list :-) )Tue, 06 Sep 2011 16:01:37 GMTantony-688446http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]antony-688446 (9/6/2011)[/b][hr]Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives? The following code[code="sql"]SELECT a.co_seqFROM F_Pm_Tenant aJOIN F_Contact b ON a.co_seq = b.co_seqORDER BY co_seq[/code]gets the result:Msg 209, Level 16, State 1, Line 4Ambiguous column name 'co_seq'.every time. What is the difference (apart from the table & column names!) between this and the example in the question?Confused :unsure:[/quote]Which version of SQL Server are you using? I tested this on 2005.Tue, 06 Sep 2011 15:51:33 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxHey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives? The following code[code="sql"]SELECT a.co_seqFROM F_Pm_Tenant aJOIN F_Contact b ON a.co_seq = b.co_seqORDER BY co_seq[/code]gets the result:Msg 209, Level 16, State 1, Line 4Ambiguous column name 'co_seq'.every time. What is the difference (apart from the table & column names!) between this and the example in the question?Confused :unsure:Tue, 06 Sep 2011 14:42:10 GMTantony-688446http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxNice question, but I got it wrong by the ambigous column name (not really the point the question was focusing on).Tue, 06 Sep 2011 14:40:28 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]archie flockhart (9/6/2011)[/b][hr]Actually, it may mean that most people have never tried using Order By in a subquery, and therefore don't know whether it will give an error or just waste a bit of processing time.[/quote]There are cases when someone will need to use a subquery with a TOP clause and, in that case, the ORDER BY will be attached to the TOP command only and not the actual order of the results. I've seen people using that before.Best regards,Tue, 06 Sep 2011 13:38:33 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxNice question. I learned a couple things.Tue, 06 Sep 2011 10:53:01 GMTcroberts 36762http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxReally good question. Even though I had run into both of these I still had to think. If 1,3 and 4 failing had been an option that would have tripped me up. I was pretty sure I saw a failure at some point where the select list only specified one column but the order by still errored if it didn't have the table specified.Tue, 06 Sep 2011 10:24:45 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxNice question. This one took a little while to analyze. I actually guessed that 2 would not fail only because it was exactly the same as 1 except for the TOP statement so I figured that must change things and make it acceptable. I knew number 1 would fail because I have made the "Order By in a subquery error" before and I knew number 4 would fail because EmployeeID would be ambiguous.Tue, 06 Sep 2011 10:15:27 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxgreat question. also surprised that only 14% had answered correctly as of a short while ago.Tue, 06 Sep 2011 09:57:15 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxnice question. thanksTue, 06 Sep 2011 09:15:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxNumber 4 got me -- IMO it is darn difficult to figure out, looking just at the query, that the ID may be ambiguous.Tue, 06 Sep 2011 09:05:25 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxNice question.. It really made me think.Tue, 06 Sep 2011 08:18:18 GMTjenal17http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]paul.knibbs (9/6/2011)[/b][hr]Knew 4 would fail, but never heard of the problem that would cause #1 to not work...always nice to learn something new! (I mean, I realised the ORDER BY in the subquery was fairly redundant, but I never realised it would actively fail if you tried it).[/quote]I have the same problem ;-)Tue, 06 Sep 2011 08:17:05 GMTJaroslav Pfauserhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote]The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.[/quote]Actually, it may mean that most people have never tried using Order By in a subquery, and therefore don't know whether it will give an error or just waste a bit of processing time. And as someone said earlier, there is no logical reason it absolutely needs to fail - the complier could conceivably be set to ignore the order by clause and produce the correct output.Also,some people (like me ! ) probably wrongly thought 3 would fail, and they know there is something wrong with 4, and therefore "3&4" must be the answer, whatever happens with 1 and 2 ...Tue, 06 Sep 2011 07:15:12 GMTarchie flockharthttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]paul.knibbs (9/6/2011)[/b][hr]I suspect most people are like me, e.g. they know it's pretty pointless ordering the subquery results, but they don't expect the query to fail entirely...I mean, there's no real reason why that should be the case if you think about it; why should returning the subquery results in a particular order break the entire query?[/quote]I never thought of it that way and I now see your point. It would be a badly written query and take additional server resources but why not allow you to do it anyway?Another example is that when I create a view, I'd like to specify the order inside the view. That's also not allowed. Why not?!?!? Suppose someone does a select from your view and then specifies an order by clause of their own. Which order by will take precedence? So that might be why it's not allowed in views but is it possible for this same precedence issue to arise with a subquery?Tue, 06 Sep 2011 07:10:06 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxThe reason I chose 3 and 4 , was because of ambiguity.It would seem to me that since you have two employeeIDs , since you are joining them.[code="sql"]SELECT a.EmployeeID FROM HumanResources.Employee aJOIN HumanResources.EmployeeAddress bON a.EmployeeID = b.EmployeeIDORDER BY EmployeeID[/code]But I guess since you don't select the second, alias b, you are fine not defining a in the order by clause.Although you have to define 'a' in the SELECT clause.If you were to select the ,b alias as such. [code="sql"]SELECT a.EmployeeID,b.EmployeeID FROM HumanResources.Employee aJOIN HumanResources.EmployeeAddress bON a.EmployeeID = b.EmployeeIDORDER BY a.EmployeeID[/code]I feel it would be good practce to define your alias's in your order by, but I guess it is not a requirement, unless you select them, example would be with the *.Totally did not even think of order by in the subquery problem with using the top N,tricky,tricky.:hehe:Tue, 06 Sep 2011 07:08:23 GMTjwbart06http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]cengland0 (9/6/2011)[/b]The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.[/quote]I suspect most people are like me, e.g. they know it's pretty pointless ordering the subquery results, but they don't expect the query to fail entirely...I mean, there's no real reason why that should be the case if you think about it; why should returning the subquery results in a particular order break the entire query?Tue, 06 Sep 2011 06:49:24 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]Tee Time (9/6/2011)[/b][hr]Cold comfort knowing that I chose the most frequently selected INCORRECT answer! :-P[/quote]To my surprise, only 14% of the people that attempted the question so far have selected the correct answer. I really thought this would be easier.The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.Tue, 06 Sep 2011 06:46:46 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxCold comfort knowing that I chose the most frequently selected INCORRECT answer! :-PTue, 06 Sep 2011 06:20:54 GMTTee Timehttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxThanks for a good question. Missed the subquery ORDER BY clause error as I never use ORDER BY in a subquery - expect all results before my final output will be in a random order. Good assumption to make with SQL. Something about relations .... :hehe:Tue, 06 Sep 2011 06:07:40 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspx[quote][b]archie flockhart (9/6/2011)[/b][hr]#3 got me; didn't know that it would identify the correct column automatically in the order by clause. I would always specify the table in these circumstances, and thought that omitting it would give the same error as #4 . But checking in BOL, and running the query, it does work OK, based on matching up a unique item in the SELECT clause.[/quote]That got me, too. Never tried to not specify the table, so wasn't aware this would work. Thanks for the lesson.[quote]Not sure how consistent the definition is, though. BOL says:[quote]In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.[/quote] But if I ORDER BY an unqualified fieldname from EmployeeAddress which is not listed in the Select , it still works. So it is allowing me to pick a field from a different table, without a table qualifier, even though the column is not listed in the SELECT.[/quote]As long as the table is included in the FROM section (directly or via a JOIN statement) and the column name (field name) is unique within [b]all[/b] tables in the FROM clause, it will work. So while the wording in BOL leaves space for improvement, it is consistent.Thanks for the question and the learning.MichaelTue, 06 Sep 2011 05:51:21 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxI had problems with ORDER BY before so that wasn't hard for me.Thank you for such a straightforward question about a very important subject.Best regards,Tue, 06 Sep 2011 05:27:54 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1170103-2809-1.aspxI agree with everyone here - good question, thanks!Tue, 06 Sep 2011 05:14:37 GMTAndy sql