CODEWITHSUNDEEP
mysqlgroup-by
user1204912
user1204912

Reputation: 11

Mysql query group latest insert

I have a table tbldevicevaluelog with the following layout:

id | ts | dc | data1 | data2

Where ts is a timestamp, dc is a devicecode. data1 and data2 are the latest value.

In total there are 130 different device codes.

The goal is to get the latest time and data1 from each available device code.

With the query below I can get a result with al the device codes and the latest time.

SELECT dc, MAX(ts) FROM tbldevicevaluelog GROUP BY dc

The question is how to also get the data1 which belongs to this latest time?

Upvotes: 1

Views: 167

Answers (1)

piotrm
piotrm

Reputation: 12356

Join your result back to your table on the same device code and timestamp.

SELECT tg.dc, tg.latest, t.data1 
FROM tbldevicevaluelog t
JOIN
( SELECT dc, MAX(ts) as latest FROM tbldevicevaluelog GROUP BY dc ) tg
ON tg.dc=t.dc AND tg.latest=t.ts

Upvotes: 1

Related Questions