Order By Clause

  • cengland0

    SSCertifiable

    Points: 6102

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

  • Ron McCullough

    SSC Guru

    Points: 63877

    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: 6098

    Nice question. Today i learn new thing.

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

  • Carlo Romagnano

    SSC-Insane

    Points: 21984

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

  • Ian_McCann

    SSCertifiable

    Points: 5154

    Good question. That woke me up.

  • Bex

    SSCrazy

    Points: 2377

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

    Bex

  • paul.knibbs

    SSCoach

    Points: 15270

    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

    #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

    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)

  • This was removed by the editor as SPAM

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    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: 9401

    I agree with everyone here - good question, thanks!

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    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,

    Best regards,

    Andre Guerreiro Neto

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

  • michael.kaufmann

    SSCrazy

    Points: 2816

    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

    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

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

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