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,

score 3 · Accepted Answer · answered Apr 27 '13 at 11:02

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

score 1 · Answer 2 · edited Apr 27 '13 at 23:32

1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE t
    ("JOB_NAME" varchar2(7), "RUNTIME" int)
;

INSERT ALL 
    INTO t ("JOB_NAME", "RUNTIME")
         VALUES ('AR_job1', 100)
    INTO t ("JOB_NAME", "RUNTIME")
         VALUES ('AR_job2', 120)
    INTO t ("JOB_NAME", "RUNTIME")
         VALUES ('AR_job3', 130)
    INTO t ("JOB_NAME", "RUNTIME")
         VALUES ('EP_job1', 100)
    INTO t ("JOB_NAME", "RUNTIME")
         VALUES ('EP_job2', 80)
SELECT * FROM dual
;

Query 1:

select max(job_name) keep (dense_rank first order by runtime desc) job
,      max(runtime) as maxruntime
from t 
group by substr(job_name, 1,2)

Results:

|     JOB | MAX(RUNTIME) |
--------------------------
| AR_job3 |          130 |
| EP_job1 |          100 |

edited Apr 27 '13 at 23:32

Andomar

232,371
49
380
404

answered Apr 27 '13 at 12:00

Kirill Leontev

10,641
7
43
49

Yey, that looks good, but how do i add the actual runtime of those jobs? i need to know that value as well, among with the code (i can add another field with substr(job_name,0,2) as Code – Smoke Apr 27 '13 at 18:26
+1 Didn't know about `keep`. I've edited your query to add the maximum runtime, as requested by Smoke, hope that's ok. – Andomar Apr 27 '13 at 23:32

Get highest value for each group of names

2 Answers2