SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order By Clause


Order By Clause

Author
Message
cengland0
cengland0
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2804 Visits: 1300
Comments posted to this topic are about the item Order By Clause
bitbucket-25253
bitbucket-25253
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26717 Visits: 25280
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
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2878 Visits: 1549
Nice question. Today i learn new thing.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9979 Visits: 3465
I immediately get it right, because I often fall in these kind of errors.
Ian_McCann
Ian_McCann
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3103 Visits: 2348
Good question. That woke me up.
Bex
Bex
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 1713
Well that got the brain cells working! Thanks, good question.

Bex
paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6352 Visits: 6240
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).
archie flockhart
archie flockhart
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 1172
#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.
malleswarareddy_m
malleswarareddy_m
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3407 Visits: 1189
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)
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27294 Visits: 7545
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”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search