Hello,

I'm hoping someone can help me as I've been stuck on this problem for a few days now. Basically I'm trying to pull data from 3 tables in Oracle: 1) Orders Table 2) Vendor Table and 3) Master Data Table.

Here's what the 3 tables look like:

Table 1: BIZ_DOC2 (Orders table)

OBJECTID (Unique key)
UNIQUE_DOC_NAME (Document Name i.e. ORD-005)
CREATED_AT (Date the order was created)

Table 2: UDEF_VENDOR (Vendors Table):

PARENT_OBJECT_ID (This matches up to the ObjectId in the Orders table)
VENDOR_OBJECT_NAME (This is the name of the vendor i.e. Acme)

Table 3: BIZ_UNIT (Master Data table)

PARENT_OBJECT_ID (This matches up to the ObjectID in the Orders table)
BIZ_UNIT_OBJECT_NAME (This is the name of the business unit i.e. widget A, widget B)

Note: The Vendors Table and Master Data do not have a link between them except through the Orders table.

I can join all of the data from the tables and it looks something like this:

Before selecting latest order date:

ORD-005 | Widget A | Acme | 3/14/10
ORD-005 | Widget B | Acme | 3/14/10
ORD-004 | Widget C | Acme | 3/10/10

Ideally I'd like to return the latest order for each vendor. However, each order may contain multiple business units (e.g. types of widgets) so if a Vendor's latest record is ORD-005 and the order contains 2 business units, here's what the result set should look like by the following columns: UNIQUE_DOC_NAME, BIZ_UNIT_OBJECT_NAME, VENDOR_OBJECT_NAME, CREATED_AT

After selecting by latest order date:

ORD-005 | Widget A | Acme | 3/14/10
ORD-005 | Widget B | Acme | 3/14/10

I tried using Select Max and several variations of sub-queries but I just can't seem to get it working. Any help would be hugely appreciated!