SubQuery

Question

Post reply

SubQuery

PChiragS

SSCarpal Tunnel

Points: 4965
More actions
March 7, 2013 at 8:42 pm

#271188

Comments posted to this topic are about the item SubQuery

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

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

demonfox SSCertifiable Points: 6289 More actions · Answer 1

nice and easy for the last day of the week ..

thanks for the question !!!

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

demonfox SSCertifiable Points: 6289 More actions · Answer 2

The first three items are required for subqueries. There are only 32 nesting levels allowed and if a TOP clause is used, an ORDER BY must be included.

actually, It's otehr way around with ORDER BY ..

An order by cannot be included in a subquery except when a TOP clause is used ..

EDIT :didn't notice that .. I opened the account :w00t:

Correct answers: 33% (1)

Incorrect answers: 67% (2)

shouldn't have come that early to office 😎

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

Must include an ORDER BY clause when a TOP clause is specified.

I think this statements should be "Must include an TOP clause when a ORDER BY clause is specified."

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

db4breakfast Ten Centuries Points: 1097 More actions · Answer 4

demonfox (3/7/2013)
The first three items are required for subqueries. There are only 32 nesting levels allowed and if a TOP clause is used, an ORDER BY must be included.
actually, It's otehr way around with ORDER BY ..
An order by cannot be included in a subquery except when a TOP clause is used ..
EDIT :didn't notice that .. I opened the account :w00t:
Correct answers: 33% (1)
Incorrect answers: 67% (2)
shouldn't have come that early to office 😎

For a subquery, it can't contain an order by unless top is used.

USE AdventureWorks;

GO

--this query run with no error

SELECT ProductID, (SELECT TOP(1) CustomerID FROM Sales.Customer)

FROM Production.Product;

--this query has error msg 1033: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

SELECT ProductID, (SELECT CustomerID FROM Sales.Customer ORDER BY CustomerID DESC)

FROM Production.Product;

edit: the answer's explanation is not correct.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.

vinu512 SSCoach Points: 15729 More actions · Answer 5

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.

Here's a simple example:

Declare @test-2 Table (a int)

Insert Into @test-2 Values(1);

Insert Into @test-2 Values(2);

Insert Into @test-2 Values(3);

Insert Into @test-2 Values(4);

Insert Into @test-2 Values(5);

Insert Into @test-2 Values(6);

Insert Into @test-2 Values(7);

Insert Into @test-2 Values(8);

Insert Into @test-2 Values(9);

Insert Into @test-2 Values(10);

--Correct SubQueries

Select Top 1 * From @test-2 Where a IN(Select Top 5 * From @test-2)

Order By a Desc

Select Top 1 * From @test-2 Where a IN(Select Top 5 * From @test-2 Order By a)

Order By a Desc

--Incorrect SubQuery(Error: Msg 1033, Level 15, State 1, Line 24 - The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.)

Select Top 1 * From @test-2 Where a IN(Select * From @test-2 Order By a)

Order By a Desc

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Looks like we may have a fun day with this question.

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

demonfox SSCertifiable Points: 6289 More actions · Answer 7

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:

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 8

Oops. Got it wrong due to overlook question :doze:

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 9

Damn it! I should have listen to my teachers mantra "read the question through before trying to answer"

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Carlo Romagnano SSC-Insane Points: 22664 More actions · Answer 10

Lokesh Vij (3/7/2013)
Must include an ORDER BY clause when a TOP clause is specified.
I think this statements should be "Must include an TOP clause when a ORDER BY clause is specified."

Steve, please, my points back: "Must include an ORDER BY clause when a TOP clause is specified." is not true!

[sql]

select top 100 * from sys.objects

WHERE exists (select top 1 object_id from sys.objects)

[/sql]

As you can try, ORDER BY is not required!

:w00t:

sfayer SSC-Addicted Points: 491 More actions · Answer 11

I Agree with Carlo,

BOL says 'may only include an ORDER BY clause when a TOP clause is also specified'.

May not must.

Steve

DugyC Hall of Fame Points: 3804 More actions · Answer 12

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.

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 13

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

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

Best Regards,

Chris Büttner

Gazareth One Orange Chip Points: 27737 More actions · Answer 14

SQLRNNR (3/7/2013)
Looks like we may have a fun day with this question.

Looks like you're right 🙂