Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries Expand / Collapse
Author
Message
Posted Monday, January 06, 2014 8:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:50 AM
Points: 1,040, Visits: 274
Comments posted to this topic are about the item Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 2: Writing Subqueries

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1528119
Posted Wednesday, January 08, 2014 4:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 9:01 AM
Points: 7, Visits: 19
Thank you for making the second part of the Stairway to T-SQL!! I have been waiting for more lessons and here they are. These are some of the best ways to learn SQL that I have seen.
Post #1529149
Posted Wednesday, January 15, 2014 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 12:14 PM
Points: 1, Visits: 5
Maybe I'm mistaken, but should (false) suggestion to Question 1 not be:

cannot be run independently of the complete query.

In stead of:
can’t not be run independently of the complete query.
Post #1531024
Posted Wednesday, January 15, 2014 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:55 AM
Points: 108, Visits: 86
I think there is a typo in Listing 1 - the subquery in the column list is counting the total orders which have a 'ModifiedDate' of 2007-02-19. Should this not be total orders which have an 'OrderDate' of 2007-02-19?
Post #1531188
Posted Wednesday, January 29, 2014 1:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 6:54 AM
Points: 55, Visits: 148
"Listing 6: Subquery in function call" looks same as Listing 5.
Post #1535748
Posted Wednesday, January 29, 2014 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 10:07 AM
Points: 2, Visits: 9
Excellent article. It definitely clarified for me how to make sure performance can be measure between different subquery syntax.
Post #1535882
Posted Wednesday, January 29, 2014 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:58 AM
Points: 1, Visits: 20
It looks like there is a mistaken assumption on the ability to reference the external query during a subquery. You can, using aliasing...

Select *, (Select Sum(OrderTotal) From Orders Where SalesmanID=S.SalesmanID)
From Salesmen S

This is a crude example (just the only one that comes to mind that is not specific to a business situation), when you would probably be better off using a direct join, but there are cases where you would not. It uses the aliased table information in the criteria.

Edit: Whoops, looks like I jumped the gun, this is covered in the 3rd article in the series..
Post #1535883
Posted Wednesday, January 29, 2014 8:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 1,625, Visits: 2,034
Others have noted some small errors in the article. However, I think that on balance it does a good job of educating people about different ways to use sub queries. I don't use all of the ways mentioned. However, it's helpful to be reminded of them. Otherwise, it's like having a nice set of tools, but having misplaced a lot of them. Thanks!

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1535954
Posted Wednesday, January 29, 2014 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 11:38 AM
Points: 6, Visits: 55
Thank you for the article. I'm not getting any results from either of the queries in listing 1. Sorry if I missed something here.

SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) RowNumber
, (SELECT COUNT(*)
FROM [Sales].[SalesOrderHeader]
WHERE ModifiedDate = '2007-02-19 00:00:00.000')
AS TotalOrders
, *
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2007-02-19 00:00:00.000';

or

SELECT COUNT(*) 
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2007-02-19 00:00:00.000'

Post #1536062
Posted Saturday, February 01, 2014 5:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:21 AM
Points: 241, Visits: 613
I think the code of Listing 5 and Listing 6 is mixed.

QUOTE
Example of using a Subquery in a Function Call
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');
Listing 6: Subquery in function call

The code in Listing 6 has two different subqueries. Both subqueries return the max OrderDate in the Sales.SalesOrderHeader table.
But the first subquery is used to pass a date to the second parameter of the DATEDIFF function.

Please correct Listing 6



Post #1537069
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse