Oracle SQL for left outer join to rownum = 1 of another query?

Question

Background

I have a query joining many tables together, the chief of which is a workorder table.

I also have a table called WOStatusHistory, which stores every status for every workorder.

A diagram of the left outer join between the two would be something like:

 +------------+           +----------------+
 |Workorder   |           |WOStatusHistory |
 |------------| LO JOIN   |----------------|
 |WONum       |+--------->|WONum           |
 |Location    |           |Status          |
 |Description |           |ChangeDate      |
 |...         |           +----------------+
 +------------+

Goal

I need to return the most recent date that the status was complete for a specific workorder (in human terms, "tell me the last time that this workorder was completed")

Process So Far

The SQL for this without returning the top record is easy enough:

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM        AS STATUSWONUM
                               , STATUS     AS STATUS
                               , CHANGEDATE AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        ORDER  BY CHANGEDATE DESC)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM ) 

;

The Issue

However, I'm having trouble figuring out how to take rownum = 1 at the appropriate time in order to return only the most recent date.

It seems my only options are (though I hope I'm wrong):

• Place rownum = 1 inside the wostatushistory query. This seems like it would limit the results before they're even linked.
• Place rownum = 1 outside of the join. This wouldn't serve the purpose, as it would give me only one record from my total returned set.

Thanks in advance for any help you can give! Happy to clarify anything.

Answer 1

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM 
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM ) 

;