Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 19, 2016 12:14 PM
Points: 1, Visits: 7
The example for Listing 6 is wrong. It has the same query as listing 5. Please correct it.
Post #1548297
Posted Tuesday, December 23, 2014 8:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 14, 2015 2:41 PM
Points: 4, 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

Post #1646194
Posted Wednesday, January 7, 2015 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 2, 2015 9:43 AM
Points: 3, Visits: 18
you might be using new version of adventure works database. change the year in order date and you will get results.
Post #1649176
Posted Tuesday, March 10, 2015 9:08 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, October 23, 2016 11:47 AM
Points: 33, Visits: 151
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.
Post #1667399
Posted Wednesday, May 6, 2015 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 19, 2016 2:03 PM
Points: 13, Visits: 59
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;
Post #1683183
Posted Friday, January 1, 2016 1:04 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 8,298, Visits: 852
I didn't realize you could use a subquery in all those places such as the HAVING clause.
Post #1749042
Posted Wednesday, May 18, 2016 10:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2016 1:57 PM
Points: 1, 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
Post #1787342
Posted Wednesday, May 18, 2016 12:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 2,379, Visits: 9,419
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.
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 on the left when you are writing a post.
How to Post Performance Problems
Post #1787360
Posted Friday, July 15, 2016 11:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 18, 2016 2:48 PM
Points: 15, 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.
Post #1802189
Posted Friday, July 15, 2016 11:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 18, 2016 2:48 PM
Points: 15, 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.
Post #1802198
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse