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

Question

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

Greg Larsen

SSC-Insane

Points: 20961
More actions
January 6, 2014 at 8:29 am

#150006

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

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

jlatorre 67460 SSC Journeyman Points: 91 More actions · Answer 1

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 Valued Member Points: 57 More actions · Answer 2

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 Old Hand Points: 383 More actions · Answer 3

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 Ten Centuries Points: 1012 More actions · Answer 4

"Listing 6: Subquery in function call" looks same as Listing 5.

sintia SSC Enthusiast Points: 126 More actions · Answer 5

Excellent article. It definitely clarified for me how to make sure performance can be measure between different subquery syntax.

danurbin SSC Enthusiast Points: 149 More actions · Answer 6

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 SSChampion Points: 10761 More actions · Answer 7

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

rstelma SSC Enthusiast Points: 187 More actions · Answer 8

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 SSCarpal Tunnel Points: 4554 More actions · Answer 9

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

lara.krefski Grasshopper Points: 19 More actions · Answer 10

The example for Listing 6 is wrong. It has the same query as listing 5. Please correct it.

jmmclaren Valued Member Points: 68 More actions · Answer 11

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 SSC Journeyman Points: 85 More actions · Answer 12

you might be using new version of adventure works database. change the year in order date and you will get results. 🙂

Katerine459 SSC Eights! Points: 945 More actions · Answer 13

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 SSC Enthusiast Points: 171 More actions · Answer 14

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;