How to get latest timestamped result from time series in a join table

Question

I have 3 tables

node
----
id
ip

metric
---
id
key (str)
node_id (FK on node)


his_metric
----
id
metric_id (FK on metric)
value (float)
datetime (datetime)

Question

How to write a query that will retrieve all metrics with their latest historic metric as a join value.

Example

===================================================================================
node_ip | metric_id | metric_key | last_hismetric_value | last_hismetric_datetime |
--------|-----------|------------|----------------------|-------------------------|
1.1.1.1 |     1     | uptime     | 24                   | 2020-04-29 19:53:10.00  |
--------|-----------|------------|----------------------|-------------------------|
1.1.1.1 |     2     | ping       | 188                  | 2020-04-29 19:53:10.00  |
--------|-----------|------------|----------------------|-------------------------|
1.1.1.1 |     3     | memory     | 512                  | 2020-04-29 19:53:10.00  |
--------|-----------|------------|----------------------|-------------------------|
2.2.2.2 |     4     | uptime     | 22                   | 2020-04-29 19:53:10.00  |
--------|-----------|------------|----------------------|-------------------------|
2.2.2.2 |     5     | ping       | 288                  | 2020-04-29 19:53:10.00  |
--------|-----------|------------|----------------------|-------------------------|
2.2.2.2 |     6     | memory     | 1024                 | 2020-04-29 19:53:10.00  |
===================================================================================

I was able to get close to my desired outcome except I cannot get the his_metric.value with the following query:

SELECT DISTINCT metric.id, node.ip, metric.key, last_hismetric.datetime FROM metric 
LEFT JOIN (select his_metric.metric_id, max(his_metric.datetime) as datetime from his_metric group by his_metric.metric_id) last_hismetric ON (metric.id = last_hismetric.metric_id)
INNER JOIN node ON (node.id = metric.node_id)
ORDER BY metric.id DESC LIMIT 100

Thank you in advance!

Answer 1

Try this one

with cte as (
select metric_id, value, datetime, ROW_NUMBER () OVER (PARTITION BY metric_id ORDER BY datetime desc) as rn
from his_metric
)

select n.ip, m.id, m.key, m_h.value, m_h.datetime
from metric as m
join node as n on m.node_id = n.id
join cte as m_h on m.id = m_h.metric_id and m_h.rn=1