title: How do I return a single row based on the aggregate of more than one column
body: <p>Sorry for the ambiguous title, not sure how to search, or ask this question.</p>
  
  <p>Lets say we have TableA:</p>
  
  <pre>
  RowID        FkId        Rank        Date
  ID1          A           1           2013-3-1
  ID2          A           2           2013-3-2
  ID3          A           2           2013-3-3
  ID4          B           3           2013-3-4
  ID5          A           1           2013-3-5
  </pre>
  
  <p>I need to create a view, that will return 1 row for each FkId. The row should be the max rank, and max date. So for FkId "A", the query would return the row for "ID3".</p>
  
  <p>I was able to return a single row by using sub-queries; first I get the MAX(Rank), then join to another query that gets MAX(Date) group by FkId &amp; Rank.</p>
  
  <pre>
  SELECT TableA.*
  (Select FkId, MAX(Rank) AS Rank FROM TableA GROUP BY FkId) s1 
  INNER JOIN (Select FkId, Rank, MAX(Date) AS Date FROM TableA GROUP BY FkId,Rank) s2 ON s1.FkId = s2.FkId AND s1.Rank = s2.Rank 
  INNER JOIN TableA ON s2.FkId = TableA.FkId AND s2.Rank = TableA.Rank AND s2.Date = TableA.Date
  </pre>
  
  <p>Is there a more efficient query that would achieve the same results? Thanks for looking.</p>
  
  <p>Edit: Added ID5 since the last answer. If I tried a normal MAX(rank),MAX(Date) GROUP BY FkId, then for "A", I would get A; 2; 2013-3-5. This result would not match up to a RowId.</p>