Order By Clause

Question

Post reply

Order By Clause

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

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

Tee Time Hall of Fame Points: 3745 More actions · Answer 1

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

cengland0 SSCertifiable Points: 6102 More actions · Answer 2

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: 15320 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

jenal17

Newbie

Points: 1

September 6, 2011 at 8:18 am

#1377431

Nice question.. It really made me think.

Revenant SSC-Forever Points: 42467 More actions · Answer 9

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: 281334 More actions · Answer 10

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 SSCarpal Tunnel Points: 4054 More actions · Answer 11

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

Ken SSCoach Points: 17191 More actions · Answer 12

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 More actions · Answer 13

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: 952 More actions · Answer 14

croberts 36762

SSC Eights!

Points: 952

September 6, 2011 at 10:53 am

#1377580

Nice question. I learned a couple things.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 15

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,

Andre Guerreiro Neto

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