如何按lp分组筛选最后时间戳且location为2的记录

需求明确：从unit_location表中，针对lp为'1234'、'9999'的分组，返回每个分组内时间戳最新的记录，且该记录的location必须是Location 2。

方法一：使用窗口函数（推荐，兼容多数现代数据库） #

利用ROW_NUMBER()窗口函数按lp分组，按timestamp倒序排序，标记每个分组内最新的记录，再筛选符合条件的结果：

SELECT id, timestamp, lp, location
FROM (
    SELECT 
        id, 
        timestamp, 
        lp, 
        location,
        -- 按lp分组，timestamp倒序，给每条记录编号，最新的为1
        ROW_NUMBER() OVER (PARTITION BY lp ORDER BY timestamp DESC) AS rn
    FROM unit_location
    WHERE lp IN ('1234', '9999')
) t
-- 筛选每个分组的第一条记录，且location为Location 2
WHERE rn = 1 AND location = 'Location 2';

方法二：关联子查询（兼容老版本数据库） #

通过子查询获取每个lp的最大时间戳，再关联原表筛选符合条件的记录：

SELECT ul.id, ul.timestamp, ul.lp, ul.location
FROM unit_location ul
WHERE ul.lp IN ('1234', '9999')
  AND ul.location = 'Location 2'
  -- 匹配当前lp的最大时间戳
  AND ul.timestamp = (
      SELECT MAX(timestamp) 
      FROM unit_location 
      WHERE lp = ul.lp
  );

关于你之前尝试失败的原因 #

直接在WHERE中使用MAX(timestamp)时，若未结合分组关联，会出现聚合函数与非聚合字段不匹配的问题，无法精准定位到对应id的最新记录，也就得不到预期结果。

内容的提问来源于stack exchange，提问作者hennybfd