PL/SQL - How to pull data from 3 tables based on latest created date

Question

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!

Answer 1

SELECT
  O.UNIQUE_DOC_NAME,
  U.BIZ_UNIT_OBJECT_NAME,
  V.VENDOR_OBJECT_NAME,
  O.CREATED_AT
 FROM
  ( SELECT
      V.VENDOR_OBJECT_NAME, MAX(O.CREATED_AT) AS CREATED_AT
     FROM
      UDEF_VENDOR AS V
     INNER JOIN
      BIZ_DOC2 AS O
     ON
      V.PARENT_OBJECT_ID=O.OBJECTID
     GROUP BY
      V.VENDOR_OBJECT_NAME
  ) AS VO   -- most recent order date per vendor
 INNER JOIN
  UDEF_VENDOR AS V
 ON
  V.VENDOR_OBJECT_NAME=VO.VENDOR_OBJECT_NAME
 INNER JOIN
  BIZ_DOC2 AS O
 ON         -- re-match vendors to orders for latest date
  O.OBJECTID=V.PARENT_OBJECT_ID AND
  O.CREATED_AT=VO.CREATED_AT
 INNER JOIN
  BIZ_UNIT AS U
 ON
  U.PARENT_OBJECT_ID=O.OBJECTID

Answer 2

SELECT DISTINCT
       FIRST_VALUE(d.unique_doc_name)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS unique_doc_name
      ,FIRST_VALUE(u.biz_unit_object_name)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS biz_unit_object_name
      ,FIRST_VALUE(v.vendor_object_name)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS vendor_object_name
      ,FIRST_VALUE(d.created_at)
       OVER (PARTITION BY v.vendor_object_name
             ORDER BY d.created_at DESC)
       AS created_at
FROM   biz_doc2 d, udef_vendor v, biz_unit u
WHERE  d.objectid = v.objectid
AND    d.objectid = u.parent_object_id;

Answer 3

Here's another solution using analytic functions. Instead of getting the first value for the max date, it ranks the dates and gets all values for the latest date.

select unique_doc_name, biz_unit_object_name, vendor_object_name, created_at
from (select unique_doc_name, biz_unit_object_name,
             vendor_object_name, created_at,
             rank () over (order by created_date desc) rnk
      from biz_doc2 d, udef_vendor v, biz_unit u
      where d.object_id = v.parent_object_id
      and d.objectid = u.parent_object_id)
where rnk = 1;