CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Maksim Nesterenko
Maksim Nesterenko

Reputation: 6213

Single inner join with MAX condition

I using SQL Server.

I have:

Table A:

    id1   
    1       
    2

Table B:

    id2     data2
    1       300
    1       400

If I will use INNER JOIN I'll get:

id1 id2 data2
1    1   300
1    1   400

But I don't need any duplicates with id1.

I can't use a LEFT JOIN because I don't need NULL values (if in table B no id2 = id1).

How can I get only one record like

id1 id2 data2
1    1   400

Maybe it's possible to use inner join with MAX(data2) function? But I don't know exactly how... And I don't need MAX from all table2, only from strings with id2 = id1 from current record.

Upvotes: 0

Views: 93

Answers (2)

kratikagandhi
kratikagandhi

Reputation: 61

You can just max the data of table 2 Here's how:

Select A.id1, A.id2 , max(B.data2) 
From A INNER JOIN B on 
A.id1=B.id2
Group By A.id1

Hope it helps!

Upvotes: 2

Jay Kazama
Jay Kazama

Reputation: 3277

Try this:

SELECT A.id1, B.id2, MAX(B.data2)
FROM A
INNER JOIN B ON A.id1 = B.id2
GROUP BY A.id1, B.id2

Upvotes: 2

Related Questions