Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why won't select acct_nbr, mail_ty, orga, pers, max(run_date) from wh_acct group by acct_nbr, mail_ty, orga, pers work? –  Colin 't Hart Aug 21 '13 at 5:24

2 Answers 2

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.

share|improve this answer
    
thanks a lot for your detailed response. Very helpful. I dont know why but when I run this, it gives me results for only those rows that don't have null in ORGA where there is a value in pers. so for acctnbr 123, it will just give me the value for pers. Weird thing is, it runs perfectly in sqlfiddle: sqlfiddle.com/#!4/8a83a/1 –  user27307 Aug 21 '13 at 16:20
    
In the Fiddle you have rundate as varchar. Why not date? Is this a typo in setting up the fiddle? –  ypercube Aug 21 '13 at 19:17
    
yes that is a typo. thanks for pointing that out! –  user27307 Aug 21 '13 at 20:08

You can use the anlytics function for this: Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [])

Examples:

http://www.orafaq.com/node/55

share|improve this answer
    
Could you be more specific? At least you should show how this relates to the question. –  dezso Aug 21 '13 at 7:56
    
Add some example code so the OP (and the rest of us dummies) can understand what you mean. –  Max Vernon Aug 30 '13 at 15:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.