CODEWITHSUNDEEP
mysqlsqldatabasejoin
Koralek M.
Koralek M.

Reputation: 3341

JOIN with MAX in WHERE

There are 2 tables:

Table temperature_now:

machine_id | temperature
------------------------
    1      |     15
    2      |     20
    3      |     13

Table temperature_history:

change_id | machine_id | temperature | controller
-------------------------------------------------
    1           1            6           Carl
    2           2            9           Steve
    3           1            7           John
    4           1            15          Peter
    5           2            20          Peter
    6           3            13          Martin

temperature_now.machine_id = temperature_history.machine_id

change_id is auto_increment

I need to get the last change of temperature at each machine:

machine_id | temperature_now | temperature_before | controller
--------------------------------------------------------------
    1              15                   7              John
    2              20                   9              Steve

Thanks for your help.

Upvotes: 0

Views: 123

Answers (3)

Scorpion
Scorpion

Reputation: 4585

You can do it using Common Table Expression as Bellow.

UPDATED

WITH History (MachineID, Temp, Controller)
AS
(
   SELECT machine_id, temperature, controller 
   FROM dbo.temperature_history
   WHERE change_id in ((SELECT MAX(change_id) FROM dbo.temperature_history AS TH
   INNER JOIN dbo.temperature_now AS TN
   ON TN.machine_id = TH.machine_id
   WHERE TN.temperature != TH.temperature 
   GROUP BY TH.machine_id))
)

SELECT N.machine_id, 
N.temperature, 
H.Temp as 'temperature_before',
H.Controller
FROM dbo.temperature_now AS N
INNER JOIN History AS H
ON H.MachineID = N.machine_id
ORDER BY N.machine_id;

Then You Will Get the Result as Bellow:

machine_id   temperature    temperature_before  Controller
1            15             7                   John
2            20             9                   Steve

Upvotes: 1

Rohan
Rohan

Reputation: 2030

This may work, but the nested queries may have performance issues. In case I can optimize the query , I will post that.


SELECT DISTINCT TN.MACHINE_ID,
                TN.TEMPERATURE,
                (SELECT DISTINCT TH.TEMPERATURE
                   FROM TEMPERATURE_HISTORY TH
                  WHERE TH.CHANGE_ID =
                        (SELECT MAX(TH1.CHANGE_ID)
                           FROM TEMPERATURE_HISTORY TH1
                          WHERE TH1.MACHINE_ID = TN.MACHINE_ID
                            AND TH1.CHANGE_ID <
                                (SELECT MAX(TH2.CHANGE_ID)
                                   FROM TEMPERATURE_HISTORY TH2
                                  WHERE TH2.MACHINE_ID = TN.MACHINE_ID))) AS TEMPERATURE_BEFORE,
                (SELECT DISTINCT TH.CONTROLLER
                   FROM TEMPERATURE_HISTORY TH
                  WHERE TH.CHANGE_ID =
                        (SELECT MAX(TH1.CHANGE_ID)
                           FROM TEMPERATURE_HISTORY TH1
                          WHERE TH1.MACHINE_ID = TN.MACHINE_ID
                            AND TH1.CHANGE_ID <
                                (SELECT MAX(TH2.CHANGE_ID)
                                   FROM TEMPERATURE_HISTORY TH2
                                  WHERE TH2.MACHINE_ID = TN.MACHINE_ID))) AS CONTROLLER
  FROM TEMPERATURE_NOW TN

Hope it helps

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171391

select n.machine_id, n.temperature as temperature_now, 
    h.temperature as temperature_before, h.controller
from temperature_now n
left outer join (
    select th.machine_id, max(th.change_id) as MaxChangeID
    from temperature_history th
    Inner join temperature_now tn on th.machine_id = tn.machine_id and th.temperature <> tn.temperature
    group by th.machine_id
) hm on n.machine_id = hm.machine_id
left outer join temperature_history h on hm.machine_id = h.machine_id
    and hm.MaxChangeID = h.change_id
order by n.machine_id

Upvotes: 0

Related Questions