[SOLVED] SQL help with MAX query

SQL help with MAX query

I have a table of countries named bbc(name, region, area, population, gdp)

I want a table with the region, name and population of the largest ( most populated) countries by region. So far i've tried this:

SELECT region, name, MAX(population)
FROM bbc
GROUP BY region

It gave me an error message : ORA-00979: Not a GROUP BY Expression

I tried to change to GROUP BY region, name, but it doesn't give me the right table

Solution

Here's the easiest and shortest way to do it, since Oracle has tuple testing, it can make the code shorter:

First, get the max population on each region:

SELECT region, MAX(population)
FROM bbc
GROUP BY region

Then test the countries against it:

select region, name, population 
from bbc 
where (region, population) in
      (SELECT region, MAX(population)
       FROM bbc
       GROUP BY region)
order by region

If you want to support many RDBMS, use EXISTS:

select region, name, population 
from bbc o
where exists
      (SELECT null -- neutral. doesn't invoke Cargo Cult Programming ;-)
       FROM bbc
       WHERE region = o.region 
       GROUP BY region
       HAVING o.population = MAX(population) )
order by region

Query tested here, both have similar output: http://sqlzoo.net/0.htm

http://www.ienablemuch.com/2010/05/why-is-exists-select-1-cargo-cult.html