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 开发者_如何学Con 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.

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