title: Select Max from each Subset
body: <p>I'm banging my head here. I feel pretty stupid because I'm sure I've done something like this before, but can't for the life of me remember how. One of those days I guess >.&lt;</p>
  
  <p>Say I have the following data: ---> and a query which returns this: ---> But I want this:</p>
  
  <pre class="lang-none prettyprint-override"><code>ID   FirstID              ID   FirstID                ID   FirstID
  --   -------              --   -------                --   -------
  1     1                   1    1                      7    1
  2     1                   3    3                      3    3
  3     3                   4    4                      6    4
  4     4                   5    5                      5    5
  5     5
  6     4
  7     1
  </code></pre>
  
  <p>Notice that my query returns the records where ID = FirstID, but I want it to return the Max(ID) <strong>for each subset</strong> of unique FirstID. Sounds simple enough right? That's what I thought, but I keep getting back just record #7. Here's my query (the one that returns the second block of figures above) with some test code to make your life easier. I need this to give me the results in the far right block. It should be noted that this is a self-joining table where FirstID is a foreign key to ID. Thanks :)</p>
  
  <pre class="lang-sql prettyprint-override"><code>declare @MyTable table (ID int, FirstID int)
  insert into @MyTable values (1,1),(2,1),(3,3),(4,4),(5,5),(6,4),(7,1)
  select ID, FirstID
  from @MyTable
  where ID = FirstID
  </code></pre>