Getting max value from rows and joining to another table

Question

Sorry if this is being stupid, I am really a newbie trying to nail this.

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 

What I want is

1 fn1 name1 2 fn2 name5 

This is what my query looks like, but it gives me multiple rows of results (instead of max) when i do the join

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 

Sub-query works fine, I get the problem on doing th join.

How do I fix this?

Thanks.

I have sql server 2008 R2

Last one is what I missed.

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 

Answer 1

What you wrote was missing A in the from clause so its not entirely clear where you went wrong but this should work

select         B.Id,         B.FileName,        A.Name FRom B      INNER JOIN A      ON A.id = B.id     INNER JOIN (            select A.Id, MAX(A.Rank)as ExpertRank            from A            group by A.Id      ) as NewA      ON a.Id = NewA.ID         AND a.Rank = NewA.ExpertRank 

See it working here

Alternatively you could use rownumber instead

WITH CTE AS  (    SELECT ID,            RANK,           Name,           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RANK DESC) rn    FROM A ) SELECT b.Id b.FileName,cte.Name FROM    b    INNER JOIN cte     ON b.id = cte.id       and cte.rn = 1 

See it working here

Answer 2

Here's an answer with JOINs instead of MAX():

SELECT DISTINCT b.id, b.filename, a1.name FROM a a1 JOIN b   ON b.id = a1.id LEFT JOIN a a2   ON a2.id = a1.id   AND a2.rank > a1.rank WHERE a2.id IS NULL 

If there are no duplicate ranks for the same id, then you don't need the DISTINCT.