Postgres：ERROR：列必须出现在GROUP BY子句中或用于聚合函数

I'm trying to construct a query that selects a groupname and the name of the CD with the highest sales. The schema looks something like

我正在尝试构建一个查询，选择一个组名和具有最高销售额的CD的名称。架构看起来像

group(groupName, groupCode)

group（groupName，groupCode）

cd(cdTitle, numberSold, groupCode)

cd（cdTitle，numberSold，groupCode）

I have constructed a query like this:

我构建了一个这样的查询：

SELECT mg.groupName, cd.cdTitle, MAX(cd.numberSold) as maxSold
FROM musicalGroup mg, cd cd
WHERE cd.groupCode = mg.groupCode
GROUP BY mg.groupCode

But I am receiving the error

但是我收到了错误

ERROR: column "cd.cdtitle" must appear in the GROUP BY clause or be used in an aggregate function

错误：列“cd.cdtitle”必须出现在GROUP BY子句中或用于聚合函数

If I remove the cd.cdtitle from the SELECT statement I am able to get the proper results that display

如果我从SELECT语句中删除cd.cdtitle，我就能得到显示的正确结果

groupname... maxsold
"Test"... 80000
"Test2"... 81000

And so forth. Is there any way for me to also retrieve the CD name from this query?

等等。我有什么方法可以从这个查询中检索CD名称吗？

1 个解决方案

#1

One approach is to use DENSE_RANK() to identify the top sellers in each musical group. Note that I chose DENSE_RANK() instead of ROW_NUMBER() because the former will allow us to identify multiple artists per group who happen to have a tie for the highest sales.

一种方法是使用DENSE_RANK（）来识别每个音乐组中的畅销书。请注意，我选择了DENSE_RANK（）而不是ROW_NUMBER（），因为前者将允许我们识别每个群体中多个艺术家，这些艺术家恰好与最高销量相关联。

WITH cte AS (
    SELECT mg.groupName,
           cd.cdTitle,
           cd.numberSold,
           DENSE_RANK() OVER (PARTITION BY cd.groupCode ORDER BY cd.numberSold DESC) dr
    FROM musicalGroup mg
    INNER JOIN cd cd
        ON cd.groupCode = mg.groupCode
)
SELECT t.groupName,
       t.cdTitle,
       t.numberSold AS maxSold
FROM cte t
WHERE t.dr = 1