January 1, 2016 at 1:04 pm
I didn't realize you could use a subquery in all those places such as the HAVING clause.
May 18, 2016 at 10:57 am
Using the subquery in Function call
it could be simply--
SELECT SalesOrderID
,OrderDate
,DATEDIFF(DAY, OrderDate, (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader)
) AS DayDiff
FROM Sales.SalesOrderHeader
May 18, 2016 at 12:04 pm
The answer to question 1 should be "None of the above." By definition, a correlated subquery is a subquery. Since none of the answers applies to ALL subqueries, there is no valid answer to question 1.
You also missed another case where subqueries can return multiple rows with a single column: on the right side of a conditional expression when used with the keyword SOME, ANY, or ALL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 15, 2016 at 11:34 am
Could someone shed more light on the following code? What kind of scenario can this be used in a real world?
SELECT TOP (SELECT TOP 1 OrderQty
FROM [Sales].[SalesOrderDetail]
ORDER BY ModifiedDate) *
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 716;
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
July 15, 2016 at 11:54 am
Upon my execution of Listing 12, I got the following results. So, I am not sure if I should agree with of using a JOIN is same as Subquery.
(1076 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 3310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 111 ms.
(1076 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 3310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 80 ms.
PS: I am using SQL Server 2014.
-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:
September 5, 2016 at 9:46 pm
What other Stairways should one take prior to this one? There's a reference to the Stairway to T-SQL DML Stairway, which I've finished, but in this article, there is also this statement:
Here are some other things to consider when using a subquery:
ntext, text and image data types are not allowed to be returned from a subqueries
The ORDER BY clause cannot be used in a subquery unless the TOP operator is used
Views that use a subquery can’t be updated
COMPUTE and INTO clauses cannot be used in a subquery
However, I've done a search on all of the Stairway to T-SQL DML articles, and there is no mention or example of the COMPUTE clause. Is there another Stairway to read prior to this one? Or is there a basic set of T-SQL commands we should know prior to starting this one?
Thanks.
September 5, 2016 at 10:52 pm
@DataJoe - I don't see anywhere that the author says what the number returned to the TOP function actually is. The example just shows that some number will be returned and used as the argument to TOP, not what that number is.
Where does it say that the number is 3?
September 6, 2016 at 11:19 am
jetboy2k (9/5/2016)
What other Stairways should one take prior to this one? There's a reference to the Stairway to T-SQL DML Stairway, which I've finished, but in this article, there is also this statement:Here are some other things to consider when using a subquery:
ntext, text and image data types are not allowed to be returned from a subqueries
The ORDER BY clause cannot be used in a subquery unless the TOP operator is used
Views that use a subquery can’t be updated
COMPUTE and INTO clauses cannot be used in a subquery
However, I've done a search on all of the Stairway to T-SQL DML articles, and there is no mention or example of the COMPUTE clause. Is there another Stairway to read prior to this one? Or is there a basic set of T-SQL commands we should know prior to starting this one?
I'm guessing that he's just listing all clauses that cannot be used in a subquery. Since COMPUTE is a clause that exists, and cannot be used in a subquery, it's included in the list. I don't think he's trying to imply that it's a clause that you should already know all about. 🙂
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply