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 1 of 6

1 2 3 4 5 »»»

Order By Clause

Rate Topic

Display Mode

Topic Options

Author

Message

cengland0

Posted Monday, September 05, 2011 9:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299

Comments posted to this topic are about the item Order By Clause

Post #1170103

bitbucket-25253

Posted Monday, September 05, 2011 9:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 5,102, Visits: 20,207

Very nice question ... took a lot of careful reading and thinking ... in fact had to spend more than a few minutes to examine each query and darn near missed one .. but since it is an error I make frequently I finally recalled why .

Thanks for a good thought provoking question.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #1170105

Danny Ocean

Posted Tuesday, September 06, 2011 12:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 1,083, Visits: 1,160

Nice question. Today i learn new thing.

Keep Learning - Keep Growing !!!
http://growwithsql.blogspot.in

Thanks
Vinay Kumar

Post #1170135

Carlo Romagnano

Posted Tuesday, September 06, 2011 1:07 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 1,972, Visits: 1,821

I immediately get it right, because I often fall in these kind of errors.

Post #1170144

440692 I am just a number

Posted Tuesday, September 06, 2011 1:21 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:46 AM
Points: 1,631, Visits: 2,031

Good question. That woke me up.

Post #1170153

Bex

Posted Tuesday, September 06, 2011 1:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 1,111, Visits: 1,394

Well that got the brain cells working! Thanks, good question.

Bex

Post #1170156

paul.knibbs

Posted Tuesday, September 06, 2011 1:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 1,258, Visits: 4,259

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

Post #1170158

archie flockhart

Posted Tuesday, September 06, 2011 2:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 1,125, Visits: 987

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

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.

Post #1170166

malleswarareddy_m

Posted Tuesday, September 06, 2011 3:39 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 1,850, Visits: 984

I did not read the 4 the option properly.so i put 1 st option and got wrong.good question.I also so many times about ambigious cloumns.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Post #1170214

Stewart "Arturius" Campbell

Posted Tuesday, September 06, 2011 5:01 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129, Visits: 4,312

Good question, reminds everyone to handle order by with caution.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1170254

« Prev Topic | Next Topic »

51 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions