Postgres按分组优先取ACTIVE=Y记录否则取最新日期记录的实现方法

方案1：最小修改你现有写法 #

只需要调整array_agg的排序规则，将ACTIVE='Y'作为最高优先级排序条件即可，完全不需要加EXISTS判断：

SELECT 
  agreement_group_id, 
  (array_agg(id ORDER BY active = 'Y' DESC, status_action_date DESC))[1] AS val
FROM ets.agreement_t 
GROUP BY agreement_group_id 
ORDER BY agreement_group_id;

原理是PostgreSQL中布尔值true排序优先级高于false，同一分组内如果存在ACTIVE='Y'的记录会被排在最前面，没有的话就会按STATUS_ACTION_DATE倒序取最新的记录，完全匹配你的需求。

方案2：性能更优的DISTINCT ON写法 #

如果你的表数据量较大更推荐这个方案，DISTINCT ON是PostgreSQL特有的语法，可以直接取分组排序后的第一条记录，不需要聚合生成完整数组，性能更好：

SELECT DISTINCT ON (agreement_group_id)
  agreement_group_id,
  id AS val
FROM ets.agreement_t
ORDER BY 
  agreement_group_id,
  active = 'Y' DESC,
  status_action_date DESC;

如果提前建联合索引(agreement_group_id, (active = 'Y') DESC, status_action_date DESC, id)，这个查询可以直接走索引扫描，不需要额外排序和聚合，效率非常高。

两种写法返回的结果都和你给出的预期输出完全一致。

内容的提问来源于stack exchange，提问作者gene b.