SQL error: How to fix invalid use of group function error?-CodePudding

I have the following query which is giving the error in the title. But I'm not sure what is the reason for this or how to fix it?

I'm trying to find the ids of countries with the presidents with the maximum salary

select c.id from country as c
inner join president as p on c.id = p.id
join president as p2
on p.salary = max(p2.salary)

Tables: President(pID: int, salary: int)

country(cID : int, pID : int)

CodePudding user response：

You are using a group aggregation function max(p2.salary) without using any grouping. You need to do the grouping in a subquery. Also you are joining the country's id with the president's id, which I assume will return no rows. Hopefully the president table has a country id to indicate the country that the president was president of.

To get a list of each country with the highest paid president for that country, use a query like this:

select c.id, c.name as country, p.name as president, p.salary 
from country as c
inner join president as p on c.id = p.countryid
inner join (
    select countryid, max(salary) as maxsalary
    from president 
    group by countryid) as p2
on c.id = p2.countryid and p.salary = p2.maxsalary

To get the country that pays their president the most, use a query like this:

select c.id, c.name as country, p.name as president, p.salary 
from country as c
inner join president as p on c.id = p.countryid
inner join (
    select max(salary) as maxsalary
    from president) as p2 on p.salary = p2.maxsalary

Note that if multiple countries pay the same as the maximum salary to their president, all these countries will be returned.

CodePudding user response：

To get the country which pays its president more than other countries pay their president...

SELECT
  *
FROM
  country    c
INNER JOIN
  president  p
    ON p.pID = c.pID
WHERE
  p.salary = (SELECT MAX(salary) FROM president)

Or...

SELECT
  *
FROM
  country    c
INNER JOIN
(
  SELECT
    *,
    RANK() OVER (ORDER BY salary DESC)  AS rn
  FROM
    president
)
  p
    ON p.pID = c.pID
WHERE
  p.rn = 1

In both cases, if multiple presidents are tied for having the highest salary, all of those presidents (and their countries) are returned.