first sorry for the silly question but I'm not an expert using SQL. I've done some search about this, but none of the answers were working for me.
I have a table with this structure:
| sensor_id | temp_value | temp_time |
|---|---|---|
| TEMP_1 | 19 | 00:00:07 |
| TEMP_1 | 13 | 01:00:00 |
| TEMP_1 | 11 | 03:10:10 |
| TEMP_1 | 15 | 07:33:49 |
| TEMP_2 | 25 | 00:00:07 |
| TEMP_2 | 20 | 01:00:00 |
| TEMP_2 | 22 | 03:10:10 |
| TEMP_2 | 28 | 07:33:49 |
I would like to get the minimum value for each sensor_id but with the corresponding time. Here is the query I do:
SELECT MIN(temp_value) as value,sensor_id,temp_time FROM nd_sensor_value
WHERE sensor_id = "TEMP_1" OR sensor_id = "TEMP_2" GROUP BY sensor_id
(I've added the sensor_id in WHERE because I have lot of other sensors in the table but I want just these 2)
With this query I get this result:
| sensor_id | temp_value | temp_time |
|---|---|---|
| TEMP_1 | 11 | 00:00:07 |
| TEMP_2 | 20 | 00:00:07 |
The values are okay, but the time isn't. It takes the min value but with the time of the first record. This is the result I expect:
| sensor_id | temp_value | temp_time |
|---|---|---|
| TEMP_1 | 11 | 03:10:10 |
| TEMP_2 | 20 | 01:00:00 |
I'm using MySQL with PHP, it is for a side project. I know that there is lots of way to do it, but my table contains a huge amount of records and I'm looking for the more optimal query.
Is Anyone able to help me with this ?
Many thanks !
