Order By Clause

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

  • 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]

  • Nice question. Today i learn new thing.

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

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

  • Good question. That woke me up.

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

    Bex

  • 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).

  • #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.

  • 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

  • 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!
  • I agree with everyone here - good question, thanks!

  • 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

  • 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

  • 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 50 total)

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