Sql inner join and max()

Question

I need your help. I have two tables, in first (sensors) there is a list of sensors and information about them, in the second table (timegetdata) there are data which were taken by these sensors and there is a time when it occurred. I need to select list of sensors, information about them and the last taken data by them. I have written a query, but it works incorrectly.

SELECT
    timegetdata.idsensor,
    sensors.type,
    sensors.lng,
    sensors.lat,
    MAX(timegetdata.time) AS time,
    timegetdata.carbon_monoxide,
    timegetdata.ammonia,
    timegetdata.alcohol,
    timegetdata.benzene,
    timegetdata.smoke,
    timegetdata.propane,
    timegetdata.butan,
    timegetdata.methane,
    timegetdata.formaldehyde,
    timegetdata.acetone,
    timegetdata.toluene
FROM sensors
INNER JOIN timegetdata 
    ON sensors.idsensor = timegetdata.idsensor
GROUP BY sensors.idsensor

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 03 '17 at 10:55
Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Shadow, May 03 '17 at 11:13

score 0 · Answer 1 · answered May 03 '17 at 10:37

Try calculating the max times from the timegetdata table in a separate subquery, then joning that subquery to the sensors table. In addition, you would need to join a second time to timegetdata to pull in the other columns from that table corresponding to the max time record for each idsensor group.

SELECT
    s.idsensor,
    s.type,
    s.lng,
    s.lat, 
    t1.time,
    t2.carbon_monoxide, 
    t2.ammonia,
    t2.alcohol, 
    t2.benzene,
    t2.smoke,
    t2.propane, 
    t2.butan,
    t2.methane,
    t2.formaldehyde, 
    t2.acetone,
    t2.toluene 
FROM sensors s
INNER JOIN
(
    SELECT idsensor, MAX(time) AS time
    FROM timegetdata
    GROUP BY idsensor
) t1
    ON s.idsensor = t1.idsensor
INNER JOIN timegetdata t2
    ON t1.idsensor = t2.idsensor

score 0 · Answer 2 · answered May 03 '17 at 10:39

0

your Query Syntax is not right, if you need to display the other columns, you have to include them in the group by expression.such as:

group by column1, column2, ..columnn

answered May 03 '17 at 10:39

rachid el kedmiri

2,376
2
18
40

score 0 · Accepted Answer · answered May 03 '17 at 10:40

You are not looking for the max(). You are looking for information from the last row. These are different.

select t.idsensor, s.type, s.lng, s.lat, 
       t.time, t.carbon_monoxide, 
       t.ammonia, t.alcohol, 
       t.benzene, t.smoke, t.propane, 
       t.butan, t.methane, t.formaldehyde, 
       t.acetone, t.toluene 
from sensors s inner join
     timegetdata t
     on s.idsensor = t.idsensor 
where t.time = (select max(t2.time)
                from timegetdata t2
                where t2.idsensor = t.idsensor
               );

Also notice how much easier to write and read the query is because it uses table aliases.

`how much easier to write and read the query` ... if you call this easy, then I'm scared to see what isn't easy :-) — Tim Biegeleisen, May 03 '17 at 10:44

Sql inner join and max()

3 Answers3