moment. I can do it with a temp table, but I think it can be done in
one query. 15 minutes of fame to the correct answer.
3 tables:
Table Continent
================Columns==================
Columns:
ID (primary key)
Name
================Data=====================
NA,North America
SA,South America
Table Country
================Columns==================
ContinentID (foreign key)
Name
Population
================Data=====================
NA,United States,295734134
NA,Canada,32805041
SA,Brazil,186112794
SA,Peru, 27269482
Can someone come up with the query to return the country with the
lowest population in each continent, ie:
CA,Canada,32805041
SA,Peru,27269482
Need to have the country name in the query (would be very easy if you
didn't!).--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
SELECT ContinentID, Name, Population
FROM Country As C
WHERE Population = (SELECT Min(Population) FROM Country
WHERE ContinentID = C.ContinentID)
ORDER BY ContinentID, Name
Do I get a Gold Star?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQtMcJYechKqOuFEgEQK58wCgyTJWEzJIGiiynKX9fRrHOA n2/5AAnjhF
XjBxajYEv1Afl+VAJVYY9DLI
=3lQX
--END PGP SIGNATURE--
russ wrote:
> This is a simplification of a query I'm attempting to write at the
> moment. I can do it with a temp table, but I think it can be done in
> one query. 15 minutes of fame to the correct answer.
> 3 tables:
> Table Continent
> ================Columns==================
> Columns:
> ID (primary key)
> Name
> ================Data=====================
> NA,North America
> SA,South America
> Table Country
> ================Columns==================
> ContinentID (foreign key)
> Name
> Population
> ================Data=====================
> NA,United States,295734134
> NA,Canada,32805041
> SA,Brazil,186112794
> SA,Peru, 27269482
> Can someone come up with the query to return the country with the
> lowest population in each continent, ie:
> CA,Canada,32805041
> SA,Peru,27269482
> Need to have the country name in the query (would be very easy if you
> didn't!).|||you most certainly do! correlated sub-query, ended up doing the same
myself.
thanks for your help
