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

related: http://stackoverflow.com/questions/9472731/postgresql-creating-a-view-of-the-most-recent-entry-for-a-given-id, http://stackoverflow.com/questions/9317597/select-values-that-having-maximum-timestamp/9319440#9319440, http://stackoverflow.com/questions/9287119/get-first-row-for-one-group/9287205#9287205 — J Cooper, Feb 28 '12 at 00:45
Actually, Can someone tell me how to deal with duplicates in this case? If I have duplicates I would like to combine name with comma and add to the row. Thanks. — user393148, Feb 28 '12 at 02:17
@user393148, that would be easy in MySQL with `GROUP_CONCAT()`. Here's the solution for [SQL Server](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column). — Marcus Adams, Feb 28 '12 at 13:44

score 39 · Accepted Answer · edited Nov 08 '19 at 16:33

39

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

edited Nov 08 '19 at 16:33

Pranay Aryal

5,208
4
30
41

answered Feb 27 '12 at 23:05

Conrad Frix

51,984
12
96
155

I edited my answer above with what worked for me. But I will also accept this as answer. Thansk again. – user393148 Feb 27 '12 at 23:13
That's an interesting use of data.SE! – Eric Jan 25 '15 at 23:56
Just what I was looking for! – Andrew Bringaze Sep 12 '16 at 15:34

score 29 · Answer 2 · answered Feb 27 '12 at 23:17

29

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.

answered Feb 27 '12 at 23:17

Marcus Adams

53,009
9
91
143

3

+1 Very nice. I've never run across that solution before. Here's a [working example](http://data.stackexchange.com/stackoverflow/query/62609/sample-3-for-9473718) – Conrad Frix Feb 27 '12 at 23:23
I tried this and it creates multiple rows for duplicate rank. How do I modify the above or my solution to take care of duplicate ids. – user393148 Feb 28 '12 at 02:28
@user393148, did you forget the `DISTINCT` keyword? The advantage here is that you don't need a subquery. – Marcus Adams Feb 28 '12 at 13:34
2

@ConradFrix, I can't take the credit. I picked this type of query up from the book [SQL Antipatterns](http://pragprog.com/book/bksqla/sql-antipatterns). With the proper indexes, this is supposed to be faster than subqueries in some cases because subqueries often negate the use of indexes. – Marcus Adams Feb 28 '12 at 13:39
1

@MarcusAdams Thanks for the link! I noticed that Bill Karin wrote the book I took a look and he has indeed [answered](http://stackoverflow.com/a/123481/119477) this type of question that way in the past. – Conrad Frix Feb 28 '12 at 15:47
4

How it works: when a1.rank is at it's maximum there then there is no a2.rank with a greater value and therefore the joined a2 row values are NULL. – Pete Alvin Feb 02 '16 at 12:27
What if I want to get the record with the max "id", not rank? I can't compare for equality and for greater than at the same time, it will never be true! – Michael Nov 15 '18 at 18:10
It doesn't work: when a2.id = a1.id, then we have necessarily a2.rank = a1.rank, so we can't have a2.rank > a1.rank – Mc 100's Feb 21 '23 at 16:23

score 1 · Answer 3 · answered Jun 02 '20 at 22:17

1

personally, i like the below. just cleaner imo (though not sure about performace)

select * 
from a
inner join b on a.id=b.id and b.rank=(select max(rank) rnk from b where id=id)

answered Jun 02 '20 at 22:17

jellz77

324
4
15

James Moore · Answer 4 · 2022-11-23T04:11:12.083

0

The easiest way and my personal preference is to do the following:

SELECT TOP (1) WITH TIES A.*
FROM A
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Rank] DESC)

It might not seem obvious to do it this way, but it works really good.

edited Nov 23 '22 at 04:11

answered Nov 23 '22 at 04:04

James Moore

31
3

Getting max value from rows and joining to another table

4 Answers4

Linked