如何规范高效查询某列最值对应行的指定列值？含Postgres场景

一、当前SQL是否符合规范？ #

你现在用的子查询写法是符合标准SQL规范的，逻辑清晰，能正确返回目标结果。但它的潜在问题是：如果first_column_pk没有索引，数据库需要扫描两次表——一次计算最大值，一次匹配对应行；即使有索引，部分数据库的查询优化器可能也会执行两次索引扫描，在数据量较大时效率不是最优。

二、标准SQL的单条SELECT实现方案 #

1. 排序后取首行（高效首选） #

标准SQL提供FETCH FIRST 1 ROW ONLY语法，配合ORDER BY可以实现单次扫描获取结果：

SELECT second_column
FROM my_table
ORDER BY first_column_pk DESC
FETCH FIRST 1 ROW ONLY;

因为first_column_pk是主键，数据库默认会为其创建唯一索引，执行时可以直接通过索引定位到最大主键的行，无需全表扫描，效率远高于子查询写法。

2. 窗口函数实现（通用场景适配） #

如果需要兼容可能存在多个相同最大值的场景（虽然主键唯一，这里仅演示通用逻辑），可以使用ROW_NUMBER()窗口函数：

SELECT second_column
FROM (
    SELECT 
        second_column,
        ROW_NUMBER() OVER (ORDER BY first_column_pk DESC) AS row_num
    FROM my_table
) t
WHERE row_num = 1;

这也是标准SQL语法，对于主键唯一的表，结果和上一种方法完全一致。

三、PostgreSQL专属优化实现 #

PostgreSQL支持两种更简洁的非标准写法：

1. 使用DISTINCT ON #

这是PostgreSQL特有的语法，按指定列去重并保留排序后的首行：

SELECT DISTINCT ON (first_column_pk) second_column
FROM my_table
ORDER BY first_column_pk DESC;

由于主键唯一，这个语句会直接返回最大主键对应的second_column值，执行效率和索引扫描一致。

2. 使用LIMIT 1 #

PostgreSQL支持用LIMIT 1替代标准SQL的FETCH FIRST 1 ROW ONLY，写法更简洁：

SELECT second_column
FROM my_table
ORDER BY first_column_pk DESC
LIMIT 1;

四、为什么你的单条SELECT尝试失败？ #

你写的WHERE first_column_pk = max(first_column_pk)语法无效，原因是聚合函数（如MAX()）不能直接用于WHERE子句。WHERE是行级过滤逻辑，执行顺序早于聚合计算；聚合函数是对整个结果集或分组后的结果集进行统计，无法在行过滤阶段直接使用。如果要基于聚合值过滤，要么用子查询，要么在分组后用HAVING（但HAVING不适用当前无分组的场景）。

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