CODEWITHSUNDEEP
mysqlsql
user3647731
user3647731

Reputation: 47

Select last two values from two IDs

I would like to select two specific values, the first value is the last inserted row where the ID_SENSOR is 1, and the second value is the last inserted row where the ID_SENSOR is 2.

My Database table:

enter image description here

My Query:

SELECT DATA FROM (SELECT * FROM registovalores WHERE ID_SENSOR = '1' OR ID_SENSOR = '2' ORDER BY ID_SENSOR DESC LIMIT 2) as r ORDER BY TIMESTAMP

My Query is printing the last value just from the ID_SENSOR 1, which it means that I'm only getting the last inserted values, and not the last inserted value from both IDS.

I would like to print my values like this:

ID_SENSOR 1 = 90
ID SENSOR 2 = 800

What do I need to change on my Query?

Thank you.

Upvotes: 1

Views: 637

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

One method uses a correlated subquery:

SELECT rv.*
FROM registovalores rv
WHERE rv.ID_SENSOR IN (1, 2) AND
      rv.TIMESTAMP = (SELECT MAX(rv2.TIMESTAMP)
                      FROM registovalores rv2
                      WHERE rv.ID_SENSOR = rv2.ID_SENSOR
                     );

Upvotes: 1

9000
9000

Reputation: 40894

You have to have two separate queries, one per sensor.

select id_sensor, data
from the_table
where id_sensor = 'sensor_1'
order by timestamp desc -- the latest value is the first to come
limit 1; -- only pick the top (latest) row.

If you want to query for more than one value in a single database roundtrip, consider using union all between several such queries.

Please note that such a query may return one row or zero rows, since data for a particular sensor may not be available yet.

Upvotes: 0

Related Questions