I have 2 tables countries and coutry_stats.

Countries table

Country_stats table

How can i find the maximum gdp / popupation along the years?

i tried

select 
  countries.country_id, 
  name, 
  country_code, 
  year, 
  gdp, 
  population, 
  max(gdp / population) as per_ratio 
from 
  countries JOIN country_stats on 
  countries.country_id = country_stats.country_id
where 
  countries.country_id = 1 group by name order by per_ratio;

but im getting this

the per_ratio is the correct maximum of this country but this is not the correct record in the database.

CodePudding user response：

This would likely yield the result you're looking for - it sorts per_ratio DESC and limits that record to one.

SELECT
   countries.country_id, 
   name, 
   country_code, 
   year, 
   gdp, 
   population, 
   gdp / population as per_ratio 
FROM countries 
JOIN country_stats 
   on countries.country_id = country_stats.country_id 
WHERE countries.country_id = 1
ORDER BY per_ratio DESC
LIMIT 1;

Alternatively, you could use a partition to first get the max for the country id in a CTE, and then pull in all records where max_per_ratio = per_ratio

WITH MAX_PER AS(
SELECT
   countries.country_id, 
   name, 
   country_code, 
   year, 
   gdp, 
   population, 
   gdp / population as per_ratio,
   MAX(gdp/population) OVER(PARTITION BY country_id) AS max_per_ratio
FROM countries 
JOIN country_stats 
   on countries.country_id = country_stats.country_id 
WHERE countries.country_id = 1)

SELECT
   country_id, 
   name, 
   country_code, 
   year, 
   gdp, 
   population, 
   per_ratio 
FROM MAX_PER
WHERE max_per_ratio = per_ratio;