Order By Clause

  • Tee Time

    Hall of Fame

    Points: 3693

    Cold comfort knowing that I chose the most frequently selected INCORRECT answer! 😛

  • cengland0

    SSCertifiable

    Points: 6102

    Tee Time (9/6/2011)


    Cold comfort knowing that I chose the most frequently selected INCORRECT answer! 😛

    To my surprise, only 14% of the people that attempted the question so far have selected the correct answer. I really thought this would be easier.

    The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.

  • paul.knibbs

    SSCoach

    Points: 15270

    cengland0 (9/6/2011)

    The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.

    I suspect most people are like me, e.g. they know it's pretty pointless ordering the subquery results, but they don't expect the query to fail entirely...I mean, there's no real reason why that should be the case if you think about it; why should returning the subquery results in a particular order break the entire query?

  • jwbart06

    Hall of Fame

    Points: 3291

    The reason I chose 3 and 4 , was because of ambiguity.

    It would seem to me that since you have two employeeIDs , since you are joining them.

    SELECT a.EmployeeID FROM HumanResources.Employee a

    JOIN HumanResources.EmployeeAddress b

    ON a.EmployeeID = b.EmployeeID

    ORDER BY EmployeeID

    But I guess since you don't select the second, alias b, you are fine not defining a in the order by clause.

    Although you have to define 'a' in the SELECT clause.

    If you were to select the ,b alias as such.

    SELECT a.EmployeeID,b.EmployeeID FROM HumanResources.Employee a

    JOIN HumanResources.EmployeeAddress b

    ON a.EmployeeID = b.EmployeeID

    ORDER BY a.EmployeeID

    I feel it would be good practce to define your alias's in your order by, but I guess it is not a requirement, unless you select them, example would be with the *.

    Totally did not even think of order by in the subquery problem with using the top N,tricky,tricky.

    :hehe:

  • cengland0

    SSCertifiable

    Points: 6102

    paul.knibbs (9/6/2011)


    I suspect most people are like me, e.g. they know it's pretty pointless ordering the subquery results, but they don't expect the query to fail entirely...I mean, there's no real reason why that should be the case if you think about it; why should returning the subquery results in a particular order break the entire query?

    I never thought of it that way and I now see your point. It would be a badly written query and take additional server resources but why not allow you to do it anyway?

    Another example is that when I create a view, I'd like to specify the order inside the view. That's also not allowed. Why not?!?!? Suppose someone does a select from your view and then specifies an order by clause of their own. Which order by will take precedence? So that might be why it's not allowed in views but is it possible for this same precedence issue to arise with a subquery?

  • archie flockhart

    SSCrazy

    Points: 2339

    The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.

    Actually, it may mean that most people have never tried using Order By in a subquery, and therefore don't know whether it will give an error or just waste a bit of processing time.

    And as someone said earlier, there is no logical reason it absolutely needs to fail - the complier could conceivably be set to ignore the order by clause and produce the correct output.

    Also,some people (like me ! ) probably wrongly thought 3 would fail, and they know there is something wrong with 4, and therefore "3&4" must be the answer, whatever happens with 1 and 2 ...

  • Jaroslav Pfauser

    SSC Eights!

    Points: 826

    paul.knibbs (9/6/2011)


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

    I have the same problem 😉

  • jenal17

    Newbie

    Points: 1

    Nice question.. It really made me think.

  • Revenant

    SSC-Forever

    Points: 42467

    Number 4 got me -- IMO it is darn difficult to figure out, looking just at the query, that the ID may be ambiguous.

  • SQLRNNR

    SSC Guru

    Points: 281252

    nice question. thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3994

    great question. also surprised that only 14% had answered correctly as of a short while ago.

  • Ken Wymore

    SSCoach

    Points: 16642

    Nice question. This one took a little while to analyze.

    I actually guessed that 2 would not fail only because it was exactly the same as 1 except for the TOP statement so I figured that must change things and make it acceptable. I knew number 1 would fail because I have made the "Order By in a subquery error" before and I knew number 4 would fail because EmployeeID would be ambiguous.

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    Really good question. Even though I had run into both of these I still had to think. If 1,3 and 4 failing had been an option that would have tripped me up. I was pretty sure I saw a failure at some point where the select list only specified one column but the order by still errored if it didn't have the table specified.

  • croberts 36762

    SSC Eights!

    Points: 942

    Nice question. I learned a couple things.

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    archie flockhart (9/6/2011)


    Actually, it may mean that most people have never tried using Order By in a subquery, and therefore don't know whether it will give an error or just waste a bit of processing time.

    There are cases when someone will need to use a subquery with a TOP clause and, in that case, the ORDER BY will be attached to the TOP command only and not the actual order of the results. I've seen people using that before.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

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

Viewing 15 posts - 16 through 30 (of 51 total)

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