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.

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:

output

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!

share|improve this question
    
Can you show what you want the output to look like please? –  Chris Saxon Mar 8 '13 at 8:36

2 Answers 2

up vote 1 down vote accepted

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;
share|improve this answer
    
I'll give it a go tomorrow. I threw in the towel after a long day today. Thanks for the suggestion. Care to elaborate on why that may work, instead of what I'm doing? Thanks! –  Kasey Mar 8 '13 at 3:17
    
This returns the highest rev_num by virtue of the MAX(rev_num) function. In order to use the MAX() function, you need to have a GROUP BY clause that collapses the results by each field in the clause. –  Max Vernon Mar 8 '13 at 3:35
    
This is a great help, but it still is pulling back two versions of event 12 and 14, I think because the weather code was changed for those. On event 133, it is returning 1 record, with the max rev number, like I had hoped. Any other suggestions? –  Kasey Mar 8 '13 at 14:48
    
Your hypothesis is correct regarding the weather code. If you remove d.weather_code from both the SELECT and the GROUP BY you will see events 12 and 14 collapse to a single record each. –  Max Vernon Mar 8 '13 at 14:50
    
is rev_num from the same table as d.weather_code ? –  Max Vernon Mar 8 '13 at 14:56

I think what you're looking for is a correlated subquery:

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 f 
                   join mv_outage_duration g on 
                        (f.restore_operation_id = g.restore_operation_id)
                  where a.eventnum = f.eventnum)

The subquery returns the max rev_num for each eventnum in the outer query. Since it's firing for each row, these can be expensive if mv_outage_duration isn't properly indexed.

share|improve this answer
    
I modified it and got it to work. I'll add the final SQL to the Question. –  Kasey Mar 8 '13 at 16:27

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.