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
Advertisement
Answer
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