Order By Clause

Question

Order By Clause

cengland0

SSCertifiable

Points: 6102
More actions
September 5, 2011 at 9:33 pm

#247575

Comments posted to this topic are about the item Order By Clause
September 6, 2011 at 5:01 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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[/url]
Before posting a performance problem please read[/url]

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

Nice question. Today i learn new thing.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 3

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

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 4

Ian_McCann

SSCertifiable

Points: 5163

September 6, 2011 at 1:21 am

#1377198

Good question. That woke me up.

Bex SSCrazy Points: 2437 More actions · Answer 5

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

Bex

paul.knibbs SSCoach Points: 15320 More actions · Answer 6

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 SSCrazy Points: 2339 More actions · Answer 7

#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 SSCertifiable Points: 5847 More actions · Answer 8

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)

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 9

good question and explanation!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 10

Andy sql

SSCrazy Eights

Points: 9496

September 6, 2011 at 5:14 am

#1377293

I agree with everyone here - good question, thanks!

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 11

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 12

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 13

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:

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn