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.