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

Order By Clause Expand / Collapse
Author
Message
Posted Tuesday, September 6, 2011 5:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:55 AM
Points: 1,262, Visits: 13,558
good question and explanation!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1170263
Posted Tuesday, September 6, 2011 5:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 8:23 AM
Points: 1,068, Visits: 1,151
I agree with everyone here - good question, thanks!
Post #1170264
Posted Tuesday, September 6, 2011 5:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:24 AM
Points: 920, Visits: 1,501
I 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,



Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Post #1170276
Posted Tuesday, September 6, 2011 5:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
archie flockhart (9/6/2011)
#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.


That got me, too. Never tried to not specify the table, so wasn't aware this would work. Thanks for the lesson.

Not sure how consistent the definition is, though. BOL says:
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.


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.



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 all 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.
Michael
Post #1170294
Posted Tuesday, September 6, 2011 6:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 1,930, Visits: 2,208
Thanks 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 ....

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1170301
Posted Tuesday, September 6, 2011 6:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 6:22 AM
Points: 531, Visits: 449
Cold comfort knowing that I chose the most frequently selected INCORRECT answer!
Post #1170308
Posted Tuesday, September 6, 2011 6:46 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
Tee Time (9/6/2011)
Cold comfort knowing that I chose the most frequently selected INCORRECT answer!

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.
Post #1170335
Posted Tuesday, September 6, 2011 6:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:32 AM
Points: 1,639, Visits: 5,721
cengland0 (9/6/2011)
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.


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?
Post #1170343
Posted Tuesday, September 6, 2011 7:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:39 PM
Points: 2,444, Visits: 424
The 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.


SELECT a.EmployeeID FROM HumanResources.Employee a
JOIN HumanResources.EmployeeAddress b
ON a.EmployeeID = b.EmployeeID
ORDER BY EmployeeID

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.
SELECT a.EmployeeID,b.EmployeeID FROM HumanResources.Employee a
JOIN HumanResources.EmployeeAddress b
ON a.EmployeeID = b.EmployeeID
ORDER BY a.EmployeeID

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.
Post #1170363
Posted Tuesday, September 6, 2011 7:10 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
paul.knibbs (9/6/2011)
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?

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?
Post #1170366
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse