Q
Problem solve Get help with specific problems with your technologies, process and projects.

Top N distinct

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.

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 last published in February 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close