0
votes

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

group(groupName, 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

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

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

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

1

1 Answers

1
votes

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.

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