Max of a sub-query within another sub-query

[sugarflux]

Hi All

This should be easy enough for a pro sql guru.. Normally i could rise to the challenge but i seem to be stuck with this one.

I have Three tables which i've greatly simplified below:

Customers
CustID | CustName | Group |
1 | Mr Brown | G1 |
2 | Mr Black | G2 |
3 | Mr Pink | G2 |
4 | Mr Yellow| G3 |

Orders
CustID|OrderDate |OrderCode|OrderSeqNum|Description |
1 |10/02/2007|B21 |10 |mashed potato|
1 |15/03/2008|B44 |20 |another order|
2 |10/06/2006|A23 |10 |blah |
2 |02/08/2006|Q44 |20 |blah again |
3 |30/04/2006|ZZ1 |10 |cabbage |
3 |11/01/2008|NM3 |20 |dizzy kipper |

RequiredOrders
Group|OrderCode|
G1 |B21 |
G1 |NM3 |
G2 |B21 |
G2 |ZZ1 |
G3 |Q44 |
G3 |B21 |
G3 |B44 |
G3 |NM3 |
G3 |ZZ1 |


I need a list of Customers from the customers table as my outer query. Then i need details of the latest order but only where the order appears in the required orders list. Follow?

So based on the above my results would be the following:
1|Mr Brown|G1|10/02/2007|B21|10|mashed potato
3|Mr Pink |G2|30/04/2006|ZZ1|10|cabage

The only other thing to mention is that the "Orders" table is approaching 9 million records....

Thanks guys

sugarflux

 

[gmmastros]

I suggest that you forget about sub-queries, especially for this. Instead, I would recommend a derived table approach. In fact, the trick here is to first select the rows in the orders table that you care about. Then, you make this query become a derived table that joins back to the other tables to return the details of those tables.

To return the max required order...

 [COLOR=blue]Select[/color] Orders.CustId, [COLOR=#FF00FF]Max[/color](OrderDate) [COLOR=blue]As[/color] MaxOrderDate
 [COLOR=blue]From[/color]   Customers
        [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] Orders
          [COLOR=blue]On[/color] Customers.CustId = Orders.CustId
        [COLOR=blue]Inner[/color] [COLOR=blue]join[/color] RequiredOrders
          [COLOR=blue]On[/color] Orders.OrderCode = RequiredOrders.OrderCode
          And Customers.CustGroup = RequiredOrders.CustGroup
 [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Orders.CustId

This query should return the correct orders, but it's missing a lot of information, right. This is where the derived table comes in.

[COLOR=blue]Select[/color] Customers.CustId, 
       Customers.CustName, 
       Customers.CustGroup, 
       Orders.OrderDate, 
       Orders.OrderCode, 
       Orders.OrderSeqNum, 
       Orders.Description
[COLOR=blue]From[/color]   Orders
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] Orders.CustId, [COLOR=#FF00FF]Max[/color](OrderDate) [COLOR=blue]As[/color] MaxOrderDate
         [COLOR=blue]From[/color]   Customers
                [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] Orders
                  [COLOR=blue]On[/color] Customers.CustId = Orders.CustId
                [COLOR=blue]Inner[/color] [COLOR=blue]join[/color] RequiredOrders
                  [COLOR=blue]On[/color] Orders.OrderCode = RequiredOrders.OrderCode
                  And Customers.CustGroup = RequiredOrders.CustGroup
         [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Orders.CustId
         ) [COLOR=blue]as[/color] A
         [COLOR=blue]On[/color] Orders.CustId = A.CustId
         And Orders.OrderDate = A.MaxOrderDate
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] Customers
         [COLOR=blue]On[/color] Orders.CustId = Customers.CustId

Notice the part in the join clause that is identical to the previous query. What you need to do is to put parenthesis around it and give it an alias (as A). Then, you can join to this as though it were just another table in your database.

Make sense?

P.S. I'm curious to know if this returns the correct rows and also what the performance is like against your 9 million row table. Can you please let me know?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
Doug needs a new pair of shoes, please click an ad link.

 

[ESquared]

There's another problem to solve: multiple orders could have the same orderdate, even if the orderdate is down to the finest-grain time resolution of your date&time column.

If that is possible, then you need to add some additional logic to handle this condition.