Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Saturday, March 5, 2016 7:55 PM
Points: 1,354, Visits: 1,300
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: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
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, July 23, 2015 2:40 AM
Points: 1,242, Visits: 1,549
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


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: Thursday, June 16, 2016 2:13 AM
Points: 3,196, Visits: 3,050
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: Wednesday, June 22, 2016 6:35 AM
Points: 1,820, Visits: 2,261
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: Tuesday, March 29, 2016 4:14 AM
Points: 1,233, Visits: 1,703
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: Tuesday, June 21, 2016 2:43 AM
Points: 1,805, Visits: 6,103
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, June 1, 2016 2:38 AM
Points: 1,246, Visits: 1,139
#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: Wednesday, July 29, 2015 11:50 PM
Points: 1,967, Visits: 1,189
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:45 AM
Points: 5,111, Visits: 6,833
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