The biggest size country per continent

[code]num continent country size

  1. Asia China 7
  2. Africa Egypt 4
  3. Europe France 5
  4. Asia Korea 1
  5. Europe Germany 6
  6. Asia Japan 3
  7. Europe Italy 2 [/code]

I have “myTable” like the above.
The code below produces the result below.

[code]code

select country from myTable
group by continent
order by size

result

Egypt
France
China
[/code]

i like to retrieve the biggest country per continent.

My target result will be the below.

Egypt Germany China
Egypt is the only country in Africa.
Germany is the biggest country in Europe.
China is the most sized country in the table.
I think I must use “max(size)”, but I can’t find to where and how I put it.

[quote=“joon1, post:1, topic:104868”]I think I must use “max(size)”, but I can’t find to where and how I put it.
[/quote]MAX(size) goes into the SELECT clause

you must also use a GROUP BY clause

I already used GROUP BY continent.

country,max(size) from myTable1 group by continent order by size
The code above still produces “Egypt, France, China”.

The following is one of my trials, but failed

country,max(size) as maxSize from myTable1 group by continent(maxSize) order by size

How can I put the word “max(size)” to the GROUP BY clause?

[quote=“joon1, post:3, topic:104868”]How can I put the word “max(size)” to the GROUP BY clause?
[/quote]you can’t, because that’s not what you want to group by

google up some sql tutorials, this is really basic stuff that you seem to have forgotten

Your database appears to be un-normalised too. This is a bad idea in the vast majority of situations.

Probably the query you are looking for is (although I haven’t tested it):

SELECT country FROM (SELECT country, MAX(size) FROM myTable GROUP BY continent) AS temp;

Do read up on some tutorials though.

If you break down what you are doing into steps, it will make sense what you are doing wrong.

  1. You group your table by continent, that means you go through your table building a subset of rows, and pick each row that has a distinct continent not already in your subset. So now you have a subset of rows:
    Asia, China, 7
    Africa, Egypt, 4
    Europe, France, 5

  2. Now you order your subset by size, in ascending order, because that is default.
    Africa, Egypt, 4
    Europe, France, 5
    Asia, China, 7

  3. Of the ordered subset, you ask to select the country
    Egypt
    France
    China

[quote=“RT_, post:5, topic:104868, full:true”]
Your database appears to be un-normalised too. [/quote]

sorry, it is fine, absolutely normalized

SELECT country FROM (SELECT country, MAX(size) FROM myTable GROUP BY continent) AS temp;

this is invalid and incorrect

[quote]Do read up on some tutorials though.[/quote]excellent idea, for you too :wink:

OK, strictly speeking it is normalised, but I meant it is not normalised to 3rd normal form or higher. I should have specified, but usually people I work with just say normalised and 3rd normal form or higher goes without saying.

3rd normal form would have the continent seperated into another table and referenced by myTable with a foreign key.

What was I thinking.

SELECT Country FROM (SELECT country, continent FROM myTable ORDER BY size DESC) AS temp GROUP BY continent;

oh yes it is :slight_smile:

what if the foreign key is the actual continent name? vwalah, still normalized here

SELECT Country FROM (SELECT country, continent FROM myTable ORDER BY size DESC) AS temp GROUP BY continent;

ORDER BY is ignored by the database engine inside subqueries

and again, country is non-aggregate (not in the GROUP BY clause) so its value is indeterminate, and not necessarily going to be associated with the maximum

Do you think that is likely? And I believe it is usually much more efficient and less wasteful to use integers as a primary key.

It does work in MySQL, I tried it.

SELECT Country FROM (SELECT country, continent FROM myTable GROUP BY size DESC) AS temp GROUP BY continent;

or

SELECT country FROM myTable AS a WHERE size=(SELECT max(size) FROM myTable AS b WHERE a.continent=b.continent);

Any Better? What query would you use r937?

le sigh

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.