March 8, 2013 at 3:09 am
DugyC (3/8/2013)
demonfox (3/7/2013)
vinu512 (3/7/2013)
The option is wrong....it is not necessary to have an Order By clause if Top is specified in a query.....it should be other way round.....Order By can only be specified in a query if Select Top is used.well, yes , it should be written the other way round.. but, still, that doesn't make the option incorrect ...
as mentioned in the question
An ORDER BY is not required if a TOP clause is used. -- stated as wrong
Must include an ORDER BY clause when a TOP clause is specified --stated as correct
I don't see anything wrong with the english :w00t:
SQLRNNR (3/7/2013)
Looks like we may have a fun day with this question.😀 definitely !!! :hehe:
I agree with vinu512...
An ORDER BY is not required if a TOP clause is used.
This is correct, you can have a TOP clause without an ORDER BY.
Must include an ORDER BY clause when a TOP clause is specified
This is wrong, you can have a TOP clause without an ORDER BY.
Frustrating to have a correct answer marked as wrong, although not exactly the end of the world. Frustrating none the less.
Quite so, this question had a correct idea and put the parts of the option the wrong way round. An ORDER BY is not required if a TOP clause is used is definitely correct. You do not need an order by to use TOP in a subquery. Must include an ORDER BY clause when a TOP clause is specified is not correct (as above).
<hulk smash> I demand a refund!
March 8, 2013 at 3:24 am
Sorry, but this question is invalid, as the author has misunderstood the statements on Subqueries in the MSDN Libraries.
Please re-award points accordingly.
Kenneth Spencer
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
March 8, 2013 at 3:49 am
As many have pointed out, the third option is incorrect and teh fifth option is incorrect - so the supposedly right answer is horribly wrong.
It would have been a good question if teh author had actually chosen the correct answers. Unfortuate that he didn't.
Tom
March 8, 2013 at 3:54 am
Got it wrong on the nesting level, so no points for me anyway. 🙂
Have a nice weekend.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2013 at 4:19 am
This was removed by the editor as SPAM
March 8, 2013 at 5:31 am
oopes...
marked wrong answer today :w00t:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 8, 2013 at 5:48 am
Frustrating yes - but I don't think that point will make or break me. The question still served a useful purpose for me - to research and prove that I wasn't really wrong at all. 🙂
March 8, 2013 at 7:58 am
SELECT
*
FROM
HumanResources.Employee
WHERE
Title IN (SELECT TOP 3 Title FROM HumanResources.Employee)
Your answer is wrong. You do not need to specify an order by when TOP is used. Who vets these questions? An order by can only be used if used with TOP.
March 8, 2013 at 8:26 am
SQLRNNR (3/7/2013)
Looks like we may have a fun day with this question.
Well.... probably.... I got it right... I noticed the backwardsness... but the other two were obviously wrong... always fun to use elimination to make sure you got it. 🙂
March 8, 2013 at 8:39 am
Christian Buettner-167247 (3/8/2013)
I like the question as it leads to some interesting observations 🙂Upfront: The MSN article is for SQL Server 2000! Here is the link to the version for SQL Server 2008 R2:
http://msdn.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx
Now a few examples:
1. TOP can be used without ORDER BY in a subquery:
SELECT * FROM (SELECT TOP 10 1 Const FROM sys.objects) SQ2. ORDER BY can be used without TOP in a subquery:
SELECT 1 WHERE EXISTS(SELECT object_idFROM sys.objects ORDER BY 1FOR XML AUTO)3. Nesting without ORDER BY up to 41(42) levels is possible:
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 object_id FROM sys.objects
))))))))))))))))))))))))))))))))))))))))
4. If you add an order by to the inner-most query, it will fail:
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 object_id FROM sys.objects ORDER BY 1
))))))))))))))))))))))))))))))))))))))))
Msg 191, Level 15, State 1, Line 16
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
5. If you add the ORDER BY anywhere else, the query succeeds
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS( SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 OBJECT_ID X FROM sys.objects WHERE EXISTS(
SELECT TOP 1 object_id FROM sys.objects
)ORDER BY 1)))))))))))))))))))))))))))))))))))))))
6. Using IN (instead of EXISTS) gives yet different results: nest level 38 / 39, depending on whether you include ORDER BY in the inner-most subquery.
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE object_id IN (
SELECT TOP 1 object_id FROM sys.objects WHERE '' IN (
SELECT TOP 1 object_id FROM sys.objects --ORDER BY 1
)))))))))))))))))))))))))))))))))))))
I am sure there are lots of other tests possible!
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Nice set of tests.
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
March 8, 2013 at 8:40 am
mtassin (3/8/2013)
SQLRNNR (3/7/2013)
Looks like we may have a fun day with this question.Well.... probably.... I got it right... I noticed the backwardsness... but the other two were obviously wrong... always fun to use elimination to make sure you got it. 🙂
Yeah, I used elimination as well. It came down to which was less wrong for me.:cool::hehe:
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
March 8, 2013 at 9:04 am
I think I got it right because I misread the third answer--apparently just like the author of the question. 🙂
March 8, 2013 at 9:08 am
Steve picked a bad day to be away from SSC!
The errors in the answer set seem to arise from a misreading of the BOL text and/or a logical error in interpreting that text. Basically, "ORDER BY requires TOP in a subquery", which is what the BOL text says, does not prove that "TOP requires ORDER BY in a subquery", which is what the QotD author asserted as true and has been disproved by very simple code examples posted by others.
This leads me to wonder: did the QotD author base his question and answers on investigation and testing, or did he just formulate a question and possible answers after reading a few lines in BOL? The question and answers all come from this single short paragraph from BOL:
The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.
which suggests to me that the QotD author just read this and put together a QotD without any further testing or investigation. While QotDs based solely on BOL do highlight information that may be obscure but helpful, they only challenge one's ability to search BOL for relevant information and are really just trivia questions. I prefer the QotDs that stimulate thought, investigation, and experimentation about how SQL Server works, whether they have to do with internal workings or T-SQL coding.
Jason Wolfkill
March 8, 2013 at 10:03 am
wolfkillj (3/8/2013)
(...) The question and answers all come from this single short paragraph from BOL (...)
You forgot the part about nesting limit - which, by the way, I don't find terribly important to know by heart whether it's 32, 64 or any other specific number. Does anyone actually push the limits of subquery nesting in real life systems?
March 8, 2013 at 10:04 am
SQLRNNR (3/7/2013)
Looks like we may have a fun day with this question.
You're right. Look like a lot of people have fun figure out this one. The question did more good than harm. Maybe the author was intended to include the incorrect explanation.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply