CODEWITHSUNDEEP
sqlsql-serverjoin
Kamod Kartik
Kamod Kartik

Reputation: 11

Select max value record from one-to-many join

I want to join two tables, but the second table contains multiple rows of parameters on which I wish to build by join.

TABLE1

+------------+-----------+
|   Ddate    |    ROOMNO |
+------------+-----------+
| 2018-22-11 |       101 |
| 2018-22-11 |       102 |
| 2018-22-11 |       103 |
| 2018-22-11 |       104 |
+------------+-----------+

TABLE2 (Multiple rows per Room No)

+------------+-----------+------------------+
|   Ddate    |    ROOMNO |  MaxVoltage      |
+------------+-----------+------------------+
| 2018-22-11 |       101 |   230            |
| 2018-22-11 |       101 |   240            |
| 2018-22-11 |       101 |   250   -----MAX |
| 2018-22-11 |       102 |   230            |
| 2018-22-11 |       102 |   255   -----MAX |
+------------+-----------+------------------+

DESIRED RESULT (I want the Max Voltage for the Room on the Ddate)

+------------+-----------+------------+
|   Ddate    |    ROOMNO | MaxVoltage |
+------------+-----------+------------+
| 2018-22-11 |       101 |        250 |
| 2018-22-11 |       102 |        255 |
| 2018-22-11 |       103 |        235 |
| 2018-22-11 |       104 |        238 |
| 2018-22-11 |       105 |        255 |
+------------+-----------+------------+

Upvotes: 0

Views: 2286

Answers (5)

Moustafa Karrar
Moustafa Karrar

Reputation: 1

First you will join between two tables in a regular way, using the date and room no. then use the aggregate function max for the voltage field with over clause , then group by Room No and Date like following 

select distinct t1.Ddate, t1.RoomNo, MAX(t2.MaxVoltage) over(partition by t1.RoomNo order by t1.Ddate) MaxVoltage
from Table1 t1
join Table2 t2 on t2.Ddate = t1.Ddate and t2.RoomNo = t1.RoomNo

Upvotes: 0

Ravi
Ravi

Reputation: 1172

select t1.dDate,t1.roomno, mvoltage from table1 t1 join
    (select Ddate ,roomno,max(MaxVoltage ) as mvoltage from table2
    group by Ddate,roomno
    ) t2 on t1.Ddate=t2.Ddate and t1.Roomno = t2.RoomNo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Use apply:

select t1.*, t2.maxvoltage
from table1 t1 outer apply
     (select top (1) t2.*
      from table2 t2
      where t2.roomno = t1.roomno and t2.ddate = t1.ddate
      order by maxvoltage desc
     ) t2;

Upvotes: 0

Tony
Tony

Reputation: 644

SELECT t2.d, t2.roomno, max(t2.maxvolt)
FROM table1 AS t1 JOIN table2 AS t2 ON t1.ddate = t2.ddate 
AND t1.roomno = t2.roomno
GROUP BY t2.d, t2.roomno;

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use subquery

    select t1.dDate,t1.roomno,mvoltage from table1 t1 join
    (select Ddate ,roomno,max(MaxVoltage ) as mvoltage from table2
    group by Ddate,roomno
    ) t2 on t1.Ddate=t2.Ddate

Upvotes: 0

Related Questions