Getting max value from rows and joining to another table
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
Solution 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
Solution 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
.