Using Max on a Table joined with another

Question

The goal is to create an overview of data records. These records come from multiple tables. One of these Tables holds several rows per record, each having an ID number. Only the one with the highest ID number should be shown.

I've tried doing some things with Inner Joins but i can't get it right.

a simplified view of the problem:

Tabel1      
Tabel1_Id   ValueA      ValueB
1           ABC         DEF
2           GHI         JKL
3           MNO         PQR
4           STU         VWX

Tabel2          
Tabel2_id   Tabel1_Id   Number      ValueC
1           1           1           Green
2           1           2           Yellow
3           2           1           Blue
4           1           3           Red
5           3           1           Purple
6           3           2           Pink
7           2           2           Violet
8           4           1           Magenta
9           2           3           Cyan
10          4           2           Teal

Desired Result      
ValueA  ValueB  ValueC
ABC     DEF     Red
GHI     JKL     Cyan
MNO     PQR     Pink
STU     VWX     Teal

Try Zaynul's query below. It will perhaps run on newer MySQL versions. — jarlh, Sep 17 '19 at 09:02
I have added tag [tag:greatest-n-per-group] You should get 100s of similar problem as your question. Also, the duplicate marked questions have answers for all the different versions of MySQL — Madhur Bhaiya, Sep 17 '19 at 09:07

score 0 · Answer 1 · answered Sep 17 '19 at 08:52

if your DBMS support row_number() then you can use below

with cte as
(select *,row_number()over(partition by Tabel1_Id order by Number desc) rn
from table2
) select valueA,valueV,valuec
 from cte join table1 t1 on cte.Tabel1_Id=t1.Tabel1_Id
  where cte.rn=1

Using Max on a Table joined with another

1 Answers1