I have table like below. Where are a lot of rows, which are in different types. I want to select only newest records in each type. There are of course records with different sensor_external_id and sensor_id values.
I need to group both by sensor_id and type. And for that group (sensor_id, type) I need select rows with latest date.
id average date sensor_external_id sensor_id type value
950 2019-10-25 20:34:23 2C3AE834ED58 124 3 25
951 25 2019-10-25 20:34:23 2C3AE834ED58 124 0 25
952 56 2019-10-25 20:34:23 2C3AE834ED58 124 2 56
953 43 2019-10-25 20:34:23 2C3AE834ED58 124 1 41
1411 2019-10-25 20:54:23 2C3AE834ED58 124 3 25
1412 24 2019-10-25 20:54:23 2C3AE834ED58 124 0 23
1413 53 2019-10-25 20:54:23 2C3AE834ED58 124 2 47
1414 41 2019-10-25 20:54:24 2C3AE834ED58 124 1 36
In this case I need exactly that result:
id average date sensor_external_id sensor_id type value
1411 2019-10-25 20:54:23 2C3AE834ED58 124 3 25
1412 24 2019-10-25 20:54:23 2C3AE834ED58 124 0 23
1413 53 2019-10-25 20:54:23 2C3AE834ED58 124 2 47
1414 41 2019-10-25 20:54:24 2C3AE834ED58 124 1 36
I was trying to group by type and date, but without success.
Any ideas?
That's a typical greatest-n per group question. A generic solution that works across most RDBMS with usually good performance is to use a correlated subquery:
select t.*
from mytable t
where t.date = (
select max(t1.date) from mytable t1 where t1.type = t.type
)
For performance, you want an index on (type, date).
If you want the latest record per type and sensor_id, then:
select t.*
from mytable t
where t.date = (
select max(t1.date)
from mytable t1
where t1.type = t.type and t1.sensor_id = t.sensor_id
)