title: select value from joined table based on lowest value in a different column
body: <p>(SQL query on Oracle DB)</p>
  
  <p>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).</p>
  
  <p>Here is a sample query that works when pull the equipment id based on priority 1 task:</p>
  
  <pre><code>   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 
  </code></pre>
  
  <p>The above query would produce the following results if it has 3 tasks:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I was trying to essentially do something along these lines but it doesn't work (gives message that group function is not allowed here):</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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.</p>