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 Monday, September 5, 2011 9:33 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
Comments posted to this topic are about the item Order By Clause
Post #1170103
Posted Monday, September 5, 2011 9:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 5,573, Visits: 24,808
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
Posted Tuesday, September 6, 2011 12:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Nice question. Today i learn new thing.



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1170135
Posted Tuesday, September 6, 2011 1:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 2,468, Visits: 2,344
I immediately get it right, because I often fall in these kind of errors.
Post #1170144
Posted Tuesday, September 6, 2011 1:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:26 AM
Points: 1,657, Visits: 2,083
Good question. That woke me up.
Post #1170153
Posted Tuesday, September 6, 2011 1:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:22 AM
Points: 1,187, Visits: 1,527
Well that got the brain cells working! Thanks, good question.

Bex
Post #1170156
Posted Tuesday, September 6, 2011 1:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
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
Posted Tuesday, September 6, 2011 2:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 2:52 AM
Points: 1,153, Visits: 1,047
#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
Posted Tuesday, September 6, 2011 3:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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
Posted Tuesday, September 6, 2011 5:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:38 PM
Points: 3,864, Visits: 5,012
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse