There are two tables with master detail relationship (PO Header details and PO part details). What I need to do is select recent 35 customers, which meet specified WHERE condition along with the details of parts ordered by them. One customer may have multiple POs in the header table. I have a cursor-based solution, which I want to discard.

    Requires Free Membership to View

    Login

What does "recent 35 customers" really mean? If it means the last 35 purchase orders, which could be fewer than 35 different customers, use this query:

select columns

  from POHeaderDetails H
inner
  join POPartDetails P
    on H.POnumber = P.POnumber
 where H.PODate in
       ( select PODate
           from POHeaderDetails H2
          where 35
              > ( select count(*)
                    from POHeaderDetails
                   where PODate > H2.PODate )
       )

However, if you want the last 35 customers who purchased anything, and the last order for each one, then use this query:

select columns
  from POHeaderDetails H
inner
  join POPartDetails P
    on H.POnumber = P.POnumber
 where H.Customer in
       ( select Customer
           from ( select Customer
                       , max(PODate) as maxPODate
                   from POHeaderDetails
                  group by Customer
                ) as C2
          where 35
              > ( select count(*)
                    from ( select Customer
                                , max(PODate) as maxPODate
                             from POHeaderDetails
                           group by Customer
                         ) as C3
                   where C3.maxPODate > C2.maxPODate )
       )
   and H.PODate =
       ( select max(PODate)
           from POHeaderDetails
          where Customer = H.Customer
       )

Caution: untested!


This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top