CODEWITHSUNDEEP
sqlsql-serverjoin
cissharp
cissharp

Reputation: 216

SQL Join to Get Row with Maximum Value from Right table

I am having problem with sql join (oracle/ms sql) I have two tables

A

ID | B_ID
---|------
1  | 1
1  | 4
2  | 3
2  | 2
----------

B

B_ID | B_VA| B_VB
-------|--------|-------
1       |    1    |    a
2       |    2    |    b
3       |    5    |    c
4       |    2    |    d
-----------------------

From these two tables I need A.ID, B.B_ID, B.B_VA (MAX), B.B_VB (with max B.B_VA) So result table would be like

ID      | B_ID | B_VA| B_VB
-------|--------|--------|-------
1       |    4    |    2    |    d
2       |    3    |    5    |    c

I tried some joins without success. Can anyone help me with query to get the result I want.

Thank you

Upvotes: 1

Views: 130

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Your logic as described doesn't quite correspond to the data. For instance, b_va is numeric, but the column in the output is a string.

Perhaps you want this. The data in a to be aggregated to get the maximum b_id value. Then each column to be joined to get the corresponding b_vb column. That, at least, conforms to your desired output:

select a.id, a.b_id, b1.b_vb as b_va, b2.b_vb
from (select id, max(b_id) as b_id
      from a
      group by id
     ) a join
     b b1
     on a.id = b1.b_id join
     b b2
     on a.b_id = b2.b_id;

EDIT:

For the corrected data, I think this is what you want:

select a.id, a.b_id, max(b1.b_va) as b_va, b2.b_vb
from (select id, max(b_id) as b_id
      from a
      group by id
     ) a join
     b b1
     on a.id = b1.b_id join
     b b2
     on a.b_id = b2.b_id
group by a.id, a.b_id, b2.b_vb;

Upvotes: 2

MauriDev
MauriDev

Reputation: 445

Try this

SELECT X.ID, Y.B_ID, X.B_VA, Y.B_VB
  FROM (SELECT A.ID, MAX(B_VA) AS B_VA
          FROM A INNER JOIN B ON A.B_ID = B.B_ID 
          GROUP BY A.ID) AS X INNER JOIN
       A AS Z ON X.ID = Z.ID INNER JOIN
       B AS Y ON Z.B_ID=Y.B_ID AND X.B_VA=Y.B_VA

Upvotes: 0

Related Questions