SQL select only rows with max value on a column

技术背景

在SQL查询中，经常会遇到需要筛选出每组中某一列具有最大值的所有行的需求。例如，在一个包含id、rev和content的表中，我们可能需要找出每个id对应的rev值最大的行及其相关信息。这是一个常见且具有挑战性的问题，不同的数据库系统可能有不同的实现方法。

实现步骤

方法一：使用子查询进行连接

1. 首先在子查询中找出每个group-identifier（这里是id）对应的max-value-in-group（这里是rev）。
2. 然后将原表与子查询进行连接，连接条件为group-identifier和max-value-in-group都相等。

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

方法二：使用自连接和筛选

1. 对表进行自连接，连接条件为group-identifier相等。
2. 第二个连接条件为左表的rev值小于右表的rev值。
3. 筛选出右表id为NULL的行，这些行即为每个id对应的rev值最大的行。

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

方法三：使用IN子查询

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

方法四：使用窗口函数

SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
             ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_order
      FROM YourTable) a
WHERE a.ranked_order = 1 

方法五：使用相关子查询

select yt.id, yt.rev, yt.contents
from YourTable yt
where rev = 
    (select max(rev) from YourTable st where yt.id=st.id)

方法六：使用MAX()分析函数

SELECT t.*
FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
WHERE t.rev = t.max_rev 

方法七：使用NOT EXIST

SELECT 
  id, 
  rev
  -- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

方法八：使用Cross Apply（仅适用于MS SQL）

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

方法九：MySQL特定方法

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

方法十：排序后分组（仅适用于MySQL）

select *
from (select * from mytable order by id, rev desc ) x
group by id

核心代码

以上各种方法的代码示例已在实现步骤中给出。

最佳实践

• 不同的方法在不同的数据库系统和数据规模下可能有不同的性能表现。建议在实际使用时进行性能测试，选择最适合的方法。
• 对于窗口函数和分析函数，它们在处理这类问题时非常方便，但需要确保数据库系统支持这些功能。
• 如果可能，为相关列创建索引可以提高查询性能，例如在(id, rev)上创建索引。

常见问题

• ONLY_FULL_GROUP_BY问题：在MySQL 8+版本中，默认启用ONLY_FULL_GROUP_BY，一些简单的GROUP BY查询可能会失败。可以使用SELECT DISTINCT和MAX()组合来解决。

SELECT DISTINCT t1.id, MAX(t1.rev), MAX(t2.content)
FROM Table1 AS t1
JOIN Table1 AS t2 ON t2.id = t1.id AND t2.rev = (
    SELECT MAX(rev) FROM Table1 t3 WHERE t3.id = t1.id
)
GROUP BY t1.id;

• GROUP_CONCAT长度限制问题：在使用MySQL特定方法时，GROUP_CONCAT有最大长度限制。可以通过设置group_concat_max_len变量来提高这个限制，但要注意对性能的影响。
• 性能问题：不同的方法在不同的场景下性能差异较大。例如，子查询在某些情况下可能会导致性能下降，特别是在数据量较大时。建议根据实际情况进行性能测试和优化。