如何编写Postgresql查询以从子行分组中选择最大值行

Question

我有一个Postgresql DB包含元数据信息。下面是演示模式：

CREATE TABLE testfiles (
    dockey SERIAL PRIMARY KEY,
    filekey VARCHAR(255) NOT NULL,
    version_num INTEGER NOT NULL,
    version_id VARCHAR(255) NOT NULL,
    size INTEGER NOT NULL,
    modified_date VARCHAR(255) NOT NULL,
    modified_by VARCHAR(255) NOT NULL
)

表中当前填充了以下信息：

# colnames=
['dockey', 'filekey', 'version_num', 'version_id', 'size', 'modified_date', 'modified_by']
-   (1, 'myDoc.txt', 1, '1a', 16, '08/09/2022', 'Jonathan')
-   (2, 'myDoc.txt', 12, '1a', 16, '08/09/2022', 'Jonathan')
-   (3, 'myDoc.txt', 13, '1b', 16, '08/08/2022', 'Jonathan')
-   (4, 'myDoc.txt', 14, '1c', 16, '08/09/2022', 'Jonathan')
-   (5, 'myDoc.txt', 15, '1d', 16, '08/08/2022', 'Jonathan')
-   (6, 'myDoc2.txt', 16, '1e', 16, '08/09/2022', 'Jonathan')
-   (7, 'myDoc2.txt', 17, '1f', 16, '08/08/2022', 'Jonathan')
-   (8, 'myDoc2.txt', 18, '1g', 16, '08/09/2022', 'Jonathan')
-   (9, 'myDoc2.txt', 19, '1h', 16, '08/08/2022', 'Jonathan')
-   (10, 'myDoc2.txt', 20, '1i', 16, '08/09/2022', 'Jonathan')

我想要做的是将文件键组合在一起，然后用Max versionNumber找到行。

因此，一个正确的输出示例是

-   (5, 'myDoc.txt', 15, '1d', 16, '08/08/2022', 'Jonathan')
-   (10, 'myDoc2.txt', 20, '1i', 16, '08/09/2022', 'Jonathan')

因为它将myDoc.txt & myDoc2.txt分组，然后为每个组找到最大版本号(15和20)。

使用普通SQL语句，我应该能够调用：

"SELECT *, MAX(version_num) FROM testfiles GROUP BY filekey"

在样例SQL DB 最小上测试时，它将完成我想做的事情。

我唯一能想到的是，上面的SELECT语句不能工作，因为我运行的是Postgresql，而不是标准的MySQL DB。

最后，这是在我们的Postgresql DB上运行SELECT语句时收到的错误：

"SELECT *, MAX(version_num) FROM testfiles GROUP BY filekey"

"error"：“testfiles.dockey列”必须出现在GROUP子句中，或者在聚合函数\n行中使用\n行:选择MAX(version_num)，*从testfiles组中按文件键\n^\n选择

非常感谢任何能帮忙的人！

Answer 1

你可以尝试使用CTE。下面的代码可能对你有用。

with cte1 as (
    select *,
          row_number() over (partition by filekey order by dockey) asrn
    from testfiles
          )
          select *
          from cte1 
          where rn in (select max(rn) from cte1)
          order by dockey

Answer 2

下面的查询将返回每个文件键具有最高版本号的行。

WITH B AS 
 (SELECT 
     filekey,            
     MAX(version_num) max_version FROM testfiles 
GROUP BY 
     filekey) 
SELECT
     A.* FROM testfiles A INNER JOIN B ON A.filekey=B.filekey AND A.version_num=B.max_version_num;

Answer 3

在Postgres中，您可以使用专用的distinct on () 运算符进行此类查询：

select distinct on (filekey) *
from testfiles 
order by filekey, version_num desc;

这将返回具有最高version_num的每个文件键一行。