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
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4445 Visits: 290
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
jlatorre 67460
jlatorre 67460
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 21
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.
Atreyu
Atreyu
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 7
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.
Peter McLean
Peter McLean
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 158
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?
endo64
endo64
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: 191
"Listing 6: Subquery in function call" looks same as Listing 5.
sintia
sintia
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 9
Excellent article. It definitely clarified for me how to make sure performance can be measure between different subquery syntax.
danurbin
danurbin
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 27
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..
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3859 Visits: 2256
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
rstelma
rstelma
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 67
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'


Henk Schreij
Henk Schreij
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 840
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



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