Using max for each sub group does not seem to work.

oracle-11gsubquery

I have a table that looks like this in Oracle 11g:

+----------------------------------------------------------+
| ACCT_NBR | MAIL_TY | ORGA      | PERS       | RUN_DATE   |
+----------------------------------------------------------+
| 123      | ALT     | 2         |            | 21-JAN-13  |
| 123      | ALT     | 2         |            | 22-FEB-13  |
| 123      | ALT     |           | 3          | 23-FEB-13  |
| 124      | PRI     | 4         |            | 24-JAN-13  |
| 124      | PRI     | 4         |            | 25-FEB-13  |
+----------------------------------------------------------+

I need to get the latest RUN_DATE based on Orga and Pers columns. So that the table looks like this: 

+----------------------------------------------------------+
| ACCT_NBR | MAIL_TY | ORGA      | PERS       | RUN_DATE   |
+----------------------------------------------------------+
| 123      | ALT     | 2         |            | 22-FEB-13  |
| 123      | ALT     |           | 3          | 23-FEB-13  |
| 124      | PRI     | 4         |            | 25-FEB-13  |
+----------------------------------------------------------+

I tried using this query but it doesn't seem to work: 

Select *  
from wh_acct   
where a.rundate = (select max(a2.rundate)  
from wh_acct a2)  
WHERE a2.ORGA = a.ORGA)
UNION
Select *  
from wh_acct   
where a.rundate = (select max(a2.rundate)  
from wh_acct a2)  
WHERE a2.PERS = a.PERS)

Can anyone point me to the right direction?

Best Answer

You have two right parenthesis more than left ones and a missing alias:

Select *  
from wh_acct a                                 -- alias added
where a.rundate = 
      (select max(a2.rundate)  
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.ORGA = a.ORGA)
UNION
Select *  
from wh_acct a                                 -- alias added 
where a.rundate = 
      (select max(a2.rundate)  
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.PERS = a.PERS) ;

You could also write the query with OR instead of UNION:

Select *  
From wh_acct a
Where a.rundate = 
      (Select max(a2.rundate)  
       From wh_acct a2   
       Where a2.ORGA = a.ORGA)
   Or a.rundate = 
      (Select max(a2.rundate)  
       From wh_acct a2   
       Where a2.PERS = a.PERS) ;

Or using window functions:

WITH cte AS
    ( SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE, 
             MAX(rundate) OVER (PARTITION BY ORGA) AS max_rundate_orga,
             MAX(rundate) OVER (PARTITION BY PERS) AS max_rundate_pers 
      FROM wh_acct
    ) 
SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE
FROM cte
WHERE rundate = max_rundate_orga
   OR rundate = max_rundate_pers ;

If, as your sample shows, all rows have NULL in orga where there is a value in pers and vice versa, you can change the UNION to UNION ALL to improve efficiency as there would be no need to check the subresults for duplicates (which is what UNION does). The window-cte version could also be rewritten as a union (ALL) of 2 subqueries.

Indexes on (orga, run_date) and (pers, run_date) would help, too.

Related Question