sql - Postgres : Getting the highest matching log associated with a record?

Question

我存储的日志如下:

LOG
ID | MODELID | EVENT
1  | 1       | Upped
2  | 1       | Downed
3  | 2       | Downed
4  | 1       | Upped
5  | 2       | Multiplexed
6  | 1       | Removed

然后我有以下模型:

MODEL
ID | NAME
1  | Model 1
2  | Model 2

我想以日志中与模型相关联的最高 ID 作为结果的日志条目结束:

NAME    | EVENT
Model 1 | Removed
Model 2 | Multiplexed

一个简单的连接给了我所有的结果:

SELECT * FROM MODEL AS M LEFT JOIN LOG AS L 
ON L.MODELID = M.ID

但这给了我所有的记录。我错过了什么？

Answer 1

试试这个

SELECT M.NAME,L.EVENT FROM LOG L INNER JOIN MODEL M
ON L.MODELID = M.ID 
WHERE L.ID IN 
(
  SELECT MAX(ID) FROM LOG GROUP BY MODELID
)