January 6, 2014 at 8:29 am
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
January 8, 2014 at 4:37 pm
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.
January 15, 2014 at 4:48 am
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.
January 15, 2014 at 9:37 am
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?
January 29, 2014 at 1:12 am
"Listing 6: Subquery in function call" looks same as Listing 5.
January 29, 2014 at 6:53 am
Excellent article. It definitely clarified for me how to make sure performance can be measure between different subquery syntax.
January 29, 2014 at 6:56 am
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..
January 29, 2014 at 8:51 am
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!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
January 29, 2014 at 11:40 am
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'
February 1, 2014 at 5:43 am
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
March 6, 2014 at 7:54 am
The example for Listing 6 is wrong. It has the same query as listing 5. Please correct it.
December 23, 2014 at 8:27 am
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
January 7, 2015 at 9:48 am
you might be using new version of adventure works database. change the year in order date and you will get results. ๐
March 10, 2015 at 9:08 pm
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.
May 6, 2015 at 9:03 am
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;
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply