title: Retrieve row of aggregate on primary key with group by
body: <p>Ok so I know you can't pull specific fields without an aggregate when you perform a SQL command with a group by, but it seems to me that if you are doing an aggregate on a primary key that is guaranteed to be unique, there should be a way to pull the other rows of that column along with it.  Something like this:</p>
  
  <pre><code>SELECT Max(id), 
         foo, 
         bar 
  FROM   mytable 
  GROUP  BY value1, 
            value2 
  </code></pre>
  
  <p>So ID is guaranteed to be unique, so it will have exactly 1 value for foo and bar, is there a way to generate a query like this?</p>
  
  <p>I have tried this, but MyTable in this case has millions of rows, and the run-time for this is unacceptable:</p>
  
  <pre><code>SELECT * 
  FROM   mytable 
  WHERE  id IN (SELECT Max(id) 
                FROM   mytable 
                GROUP  BY value1, 
                          value2) 
         AND ...
  </code></pre>
  
  <p>Ideally I would like a solution that works at least as far back as SQL server 2005, but if there are better solutions in the later versions I would like to hear them as well.</p>