Order By Clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Cary England » Order By Clause

51 posts, Page 2 of 6

««1 2 3 4 5 »»»

Order By Clause

Rate Topic

Display Mode

Topic Options

Author

Message

rfr.ferrari

Posted Tuesday, September 06, 2011 5:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 10:51 AM
Points: 1,219, Visits: 13,507

good question and explanation!

rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!

Post #1170263

Andy sql

Posted Tuesday, September 06, 2011 5:14 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 6:39 AM
Points: 893, Visits: 871

I agree with everyone here - good question, thanks!

Post #1170264

codebyo

Posted Tuesday, September 06, 2011 5:27 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 5:30 AM
Points: 861, Visits: 1,436

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

Post #1170276

michael.kaufmann

Posted Tuesday, September 06, 2011 5:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263, Visits: 1,079

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

Thomas Abraham

Posted Tuesday, September 06, 2011 6:07 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 1,146, Visits: 1,448

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 .... Hehe

Please don't go. The drones need you. They look up to you.

Post #1170301

Tee Time

Posted Tuesday, September 06, 2011 6:20 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:27 AM
Points: 321, Visits: 236

Cold comfort knowing that I chose the most frequently selected INCORRECT answer! Tongue

Post #1170308

cengland0

Posted Tuesday, September 06, 2011 6:46 AM

Ten 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! Tongue

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

paul.knibbs

Posted Tuesday, September 06, 2011 6:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:56 AM
Points: 1,256, Visits: 4,253

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

jwbart06

Posted Tuesday, September 06, 2011 7:08 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:26 AM
Points: 2,197, Visits: 374

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.
Hehe

Post #1170363

cengland0

Posted Tuesday, September 06, 2011 7:10 AM

Ten 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 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 »

51 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions