Get highest value for each group of names

Question

I have the following table as an example:

Job_name   RunTime
AR_job1      100
AR_job2      120
AR_job3      130
EP_job1      100
EP_job2       80

Job field is just text and Runtime is an integer value How do i select the biggest runtime job for those grouped by the first two letter of JOB but keep the job as well in another column?

I was able to do the following query, but then i have no idea whats the job full name for that runtime

select substr(job_name,0,2) Code, MAX(RunTime) 
FROM table1 group by substr(job_name,0,2)

This is for a query that should run in Oracle 10/11g Regards,

Answer 1

You could use row_number() to partition by the first two characters of job name. What that does is hand out an incremental number to each row with the same first two characters. The row with number one will have the highest runtime:

select  *
from    (
        select  row_number() over (
                    partition by substr(job_name,0,2) 
                    order by RunTime desc) as rn
        ,       Job_name
        ,       RunTime
        from    YourTable
        ) SubQueryAlias
where   rn = 1