SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries


Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries

Author
Message
lara.krefski
lara.krefski
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
The example for Listing 6 is wrong. It has the same query as listing 5. Please correct it.
jmmclaren
jmmclaren
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 35
lara.krefski (3/6/2014)
The example for Listing 6 is wrong. It has the same query as listing 5. Please correct it.


I think the author meant something like:

Select
OrderDate,
DateDiff(day,OrderDate,(Select MAX(OrderDate) from Sales.SalesOrderHeader)) 'Days to Max OrderDate',
(Select MAX(OrderDate) from Sales.SalesOrderHeader) 'Max Order Date'
from Sales.SalesOrderHeader


satishk.kotipalli
satishk.kotipalli
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 18
you might be using new version of adventure works database. change the year in order date and you will get results. :-)
Katerine459
Katerine459
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 156
Agree with the others; listings 5 and 6 have been switched. Which led to a lot of confusion, as I'm looking at this:

To demonstrate using a subquery in a function call, suppose that you have the requirement to display the number of days between the OrderDate and the maximum OrderDate for each Sales.SalesOrderHeader records. The code in Listing 6 meets this requirement.

SELECT count(*), OrderDate 
FROM [Sales].[SalesOrderHeader]
GROUP BY OrderDate
HAVING count(*) >
(SELECT count(*)
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2006-05-01 00:00:00.000');



...and thinking... "Wait. That makes no sense at all. Where does it grab any number of days? It looks like this... grabs all the order dates and the number of orders on those dates, where the number of orders is greater than the number of orders on 5/1/2006. Which isn't even remotely what it says it's supposed to do..."

If I'd been a total newbie, this would have been misleading. As it was, it was just confusing.
DataJoe
DataJoe
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 70
Need help understanding the results of the Top
Should the output not be 4 records and not 3?
For ProductID 0f 716 and the order by making 7/1/15 at top there was a quanity ordered of 4 on one record


SELECT TOP (SELECT TOP 1 OrderQty
FROM [Sales].[SalesOrderDetail]
ORDER BY ModifiedDate) *
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 716;
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22144 Visits: 885
I didn't realize you could use a subquery in all those places such as the HAVING clause.
himalaya.dua
himalaya.dua
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 77
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
drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15179 Visits: 11199
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Bangaaram
Bangaaram
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 51
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
Bangaaram
Bangaaram
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 51
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search