CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
pablo
pablo

Reputation: 13

select value from joined table based on lowest value in a different column

(SQL query on Oracle DB)

I'm trying to display an equipment id number on a job record based on the highest priority (lowest number) of all tasks associated with the job. I also need to display all tasks for the job so I cannot simply limit the query to the task with the highest priority. As the highest priority is 1 (with 2 as second highest and so on), querying for the task with priority 1 would always work. The problem is that sometimes the task with priority 1 gets deleted and so the priority 2 task becomes the highest priority (you cannot have duplicate priorities and priority is always an integer).

Here is a sample query that works when pull the equipment id based on priority 1 task:

   SELECT j.title, 
          j.jobnum,  
          a.eqid, 
          a.prior, 
          a.desc, 
          b.eqid peqid  
     FROM JOB j  
LEFT JOIN TASK a ON a.jobnum = j.jobnum
LEFT JOIN TASK b ON b.jobnum = j.jobnum
                AND b.prior = 1
    WHERE j.jobnum = '123'
 ORDER BY a.prior

The above query would produce the following results if it has 3 tasks:

TITLE   JOBNUM   EQID   PRIOR   DESC            PEQID
newjob  123      HAQ7   1       fix this        HAQ7
newjob  123      PDL    2       clean this      HAQ7
newjob  123      ACCH   3       move this       HAQ7

However, if task with priority 1 is deleted from the job you now must find the lowest priority task to assign the job an equipment id.

I was trying to essentially do something along these lines but it doesn't work (gives message that group function is not allowed here):

select job.title, job.jobnum, task.eqid, task.prior, task.desc, tp.eqid peqid  
from job  
left join task on job.jobnum = task.jobnum  
left join task tp on job.jobnum = tp.jobnum  
   and tp.prior = min(tp.prior)

I researched using a subquery in the join with a group function but can never seem to find one that works for what I'm trying to accomplish. Any help would be greatly appreciated.

Upvotes: 1

Views: 6535

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

Maybe something like this?

select job.title, job.jobnum, task.eqid, task.prior, task.desc, tp.eqid peqid  
from job  
left join task on job.jobnum = task.jobnum  
left join (
    select jobnum, MIN(prior) prior
    from task
    group by jobnum) m on m.jobnum = job.jobnum
left join task tp on m.jobnum = tp.jobnum and m.prior = tp.prior
where job.jobnum = '123'
order by task.prior asc

This two-level subquery works in SQL Server, it may also work in Oracle

select job.title, job.jobnum, task.eqid, task.prior, task.desc,
    (select tp2.eqid from task tp2
     where tp2.jobnum=job.jobnum and tp2.prior =
        (select MIN(tp.prior) from task tp
         where tp.jobnum=job.jobnum)) peqid
from job  
left join task on job.jobnum = task.jobnum  
where job.jobnum = '123'
order by task.prior asc

Upvotes: 4

Related Questions