title: Getting max value from rows and joining to another table
body: <p>Sorry if this is being stupid, I am really a newbie trying to nail this. </p>
  
  <pre><code>Table A:
  ID  Rank Name
  1   100  Name1
  1    45  Name2
  2    60  Name3
  2    42  Name4
  2    88 Name5
  
  Table B:
  ID FileName
  1  fn1
  2  fn2
  </code></pre>
  
  <p>What I want is </p>
  
  <pre><code>1 fn1 name1
  2 fn2 name5
  </code></pre>
  
  <p>This is what my query looks like, but it gives me multiple rows of results (instead of max) when i do the join</p>
  
  <pre><code>select B.Id B.FileName,A.Name
  FRom B
  JOIN ( 
  select A.Id, MAX(A.Rank)as ExpertRank 
  from A 
  group by A.Id
  ) as NewA on A.Id = B.ID 
  join B on A.Rank = NewA.Rank
  </code></pre>
  
  <p>Sub-query works fine, I get the problem on doing th join.</p>
  
  <p>How do I fix this?</p>
  
  <p>Thanks.</p>
  
  <p>I have sql server 2008 R2</p>
  
  <p>Last one is what I missed.</p>
  
  <pre><code>select B.Id B.FileName,A.Name 
  FRom B 
  JOIN (  
  select A.Id, MAX(A.Rank)as ExpertRank  
  from A  
  group by A.Id 
  ) as NewA on A.Id = B.ID  
  join B on A.Rank = NewA.Rank 
  and A.Id = newA.Id
  </code></pre>