Efficient alternative to subqueries in SQL Server
<p>Take the following two mock tables:</p>

<blockquote>
  <p>dbo.animal</p>
</blockquote>

<pre><code>id  name
1   dog
2   cat
</code></pre>

<blockquote>
  <p>dbo.animal_food</p>
</blockquote>

<pre><code>id  animal_id  food_id  active
1   1          4        1
2   1          5        1
</code></pre>

<p>I need to query against <code>animal</code> with multiple subqueries of <code>animal_food</code> based on different sorted results. Something like this:</p>

<pre><code> select name, 
        (   select top 1 food_id 
              from animal_food 
             where animal_id = animal.id 
             order by food_id desc) as max_food_id,
        (   select top 1 food_id 
              from animal_food 
             where animal_id = animal.id 
               and active = 1
             order by food_id desc) as max_active_food_id,
  from animal
</code></pre>

<p>This is obviously very inefficient---I need to speed it up. However, I can't figure out how to refactor this into a join that would improve performance.</p>