SQL Syntax error

sirpsycho

Can anyone point out the error in the following code? I can't figure it out...

SELECT mp.Year, mp.Country, mp.Population
FROM tbl_Countries mp,
(SELECT max(Population) as Max_Pop, Country
FROM tbl_Countries
GROUP BY Country) maxresults
WHERE mp.Country = maxresults.Country
AND mp.Population= maxresults.Max_Pop;

One table called tbl_countries with the fields Year, Country, Population. I'm basically trying to figure out for each year in the country table, list the year together with the name of each country that has the maximum population for that year.

some of the entries are:

Year | Country | Population
1960 | Ireland | 1500
1980 | Ireland | 2100
1960 | Uk | 1234
1980 | Uk | 12345

Merrion

SELECT mp.Year, mp.Country, mp.Population
  FROM tbl_Countries mp
  WHERE NOT EXISTS (SELECT 'A' FROM TBL_COUNTIRES ma
                                    WHERE ma.YEAR = mp.YEAR
                                         AND ma.POPULATION > mp.POPULATION)

..should do it....although it will return more than one country if they have the same population.

sirpsycho

what's the "SELECT 'A' " part about??

Merrion

It returns an 'a' from the table whenever it contains the same year but a higher population.

The NOT EXISTS then excludes these from the first list.

This leaves the ones you want (yellow in the attached Venn diag.)

sirpsycho

thanku very much!

sirpsycho

Something similar for this one??

for each country in the POPULATION table, list its name together with the change in population between 1960 and 1980. (your result should have the column headings ‘country and ‘pop_change’.



The table for population looks like this…



country year population(in 1000s)

eire 1960 2834.00

france 1960 45000.00

eire 1980 3400.00

france 1980 537000

100gSoma

test

100gSoma

hi lads,
I just lost a 2 page post about this!!! ARGH
anyways heres roughly what I said...

I am doing this SAME project form y OU sirPsycho. Recognise the code... Im actually stuck similarly on this one. Heres what I have so far though...

Basically I was using the MAX and MIN to find out the population change between 1960 and 1980 for each country. got it working but the problem is that Portugals population feel in 1970 below 1960 levels so my query was actually working out the change in population between 1970 and 1980 for portrugal.

It needs to be something to do with the year I thnk as opposed to min and max.
Here is what the table looks like... (there is only 3 years for each country)

country year population
greece 1960 3000
germany 1960 4000
greece 1970 2700
germany 1970 4200
greece 1980 3500
germany 1980 4500

This is waht Im working on now, although it might be the wrong idea? anyone?

Thanks

select country, (population) as pop_change
from population
where pop_change = (select (population) as pop_change
where pop_change is (yr ='1980') - (yr = '1960')
from population
group by country) ;

100gSoma

hi Merrion,

I got this to work using similar code. Im just wondering does it break out of the loop as soon as there is a lower population? Or will it check each country in the countries table for a given year? It just ahppens that the first country in the countries table is also the largest population in each year.

The code is

SELECT mp.yr, mp.country, mp.population
FROM population mp
WHERE NOT EXISTS (SELECT 'A' FROM population ma
WHERE ma.yr = mp.yr
AND ma.population > mp.population);

Originally posted by Merrion

SELECT mp.Year, mp.Country, mp.Population
  FROM tbl_Countries mp
  WHERE NOT EXISTS (SELECT 'A' FROM TBL_COUNTIRES ma
                                    WHERE ma.YEAR = mp.YEAR
                                         AND ma.POPULATION > mp.POPULATION)

..should do it....although it will return more than one country if they have the same population.

100gSoma

hi Merrion.

Ignore last post. Just changed some totals to see, it works perfectly. thanks....