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

  • 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

  • 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.

  • 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.

  • 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?

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

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

  • 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..

  • 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

  • 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'

  • 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

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

  • 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

  • you might be using new version of adventure works database. change the year in order date and you will get results. ๐Ÿ™‚

  • 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.

  • 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