SQL Max() revision number This SQL returns a table with everything unique, which is what I want.  However, if there are two (or more) event numbers, I need it to return the line with the highest rev_num for that eventnum. 

I have read about the max() function, but I'm not having any success with it. 

select distinct a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, rev_num
from mv_outage_duration a
    inner join mv_outhist_transformers b
        on a.restore_operation_id = b.phase_restored_operation_id
    inner join mv_aeven d
        on d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    inner join mv_we_outage_premise e
        on e.xfmr = b.xfmr_name
where e.we_premise = '995184'


Here's what the output looks like: 



FINAL ANSWER: 

First off, both answers helped immensely. The final query looks like this: 

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, rev_num
from mv_outage_duration a
    inner join mv_outhist_transformers b
        on a.restore_operation_id = b.phase_restored_operation_id
    inner join mv_aeven d
        on d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    inner join mv_we_outage_premise e
        on e.xfmr = b.xfmr_name
where e.we_premise = '995184'
  and rev_num = (select max(rev_num) 
                   from mv_aeven d 
                  where d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID)


Max did a great job of explaining why I should restructure my SQL in a way that uses the inner joins. Marty was correct in that I needed a subquery on the where clause, just needed to point to the right columns for the data I needed.  This could have been my fault as I'm not sure I explained the data structure well enough. 

Thank you both for your time and efforts!
Have you tried something like this?  Since I don't have your actual tables (nor do I have time to fake them up), I cannot test this.  However, this might help point you in the correct direction.  Failing that, you may want to look at common-table-expressions, or CTE's.

SELECT a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, MAX(rev_num) AS MaxRevNum
FROM mv_outage_duration a
    INNER JOIN mv_outhist_transformers b
        ON a.restore_operation_id = b.phase_restored_operation_id
    INNER JOIN mv_aeven d
        ON d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    INNER JOIN mv_we_outage_premise e
        ON e.xfmr = b.xfmr_name
WHERE e.we_premise = '995184'
GROUP BY a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code




EDIT;  perhaps this will help

SELECT a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, MAX(rev_num) AS MaxRevNum
FROM mv_outage_duration a
    INNER JOIN mv_outhist_transformers b
        ON a.restore_operation_id = b.phase_restored_operation_id
    INNER JOIN mv_aeven d
        ON d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    INNER JOIN mv_we_outage_premise e
        ON e.xfmr = b.xfmr_name
WHERE e.we_premise = '995184'
    AND d.weather_code IN (SELECT d.weather_code FROM mv_aeven WHERE mv_aeven.rev_num = MaxRevNum)
GROUP BY a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code;