title: How to refactor select subqueries into joins?
body: <p>I have an extremely ugly complex query that returns results for a race based on a series of groupings.</p>
  
  <p>I have a table that contains registration information (name, etc), and a table that contains finish times. I need to get a place for each race, class, and division (they are sub groups of each other).</p>
  
  <p>What I have working right now is this:</p>
  
  <pre><code>Select reg.name, r.fin_time
    ( select count(*)+1 from 
      ( Select temp_reg.id as regid, temp_reg.race_id as in_race, temp_res.division as in_div, 
           COALESCE(temp_res.fin_time, 9999999999) as finish_time 
        FROM results as temp_res Inner JOIN registration as temp_reg on temp_res.registration_id = temp_reg.id 
        WHERE COALESCE(temp_res.fin_time) &gt; 0 
          AND temp_res.status ='Finished') as ahead_div 
      WHERE ahead_div.regid &lt;&gt; reg.registration_id 
        AND ahead_div.in_race = reg.race_id 
        AND ahead_div.in_div = r.division 
        AND ahead_div.finish_time &lt; COALESCE(r.fin_time, 9999999999) ), '-') as fin_place_div,
  ... same for class and race...
  FROM results as r inner join registration as reg on r.registration_id = reg.id 
  WHERE r.status = 'Finished';
  </code></pre>
  
  <p>So I am collecting all the competitors that are finished, and counting them where they are in the same race and same div, and their finish time is &lt; the finish time of this record.</p>
  
  <p>I now have a situation where I need the calculated race finish place to be displayed as the class finish place.. so I need to either duplicate the race position subquery in an if statement (uglier and slow), or move the subqueries to the where clause so I can use them more than once.</p>
  
  <p>How would I reformat this query from a "count() from subquery where" single value subquery in the select to a join?</p>
  
  <p>Is there some what I can reuse the inner subquery? if in add the class to it, I could count from it for every place counting.</p>
  
  <p>Cheers,</p>