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
[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
If you break down what you are doing into steps, it will make sense what you are doing wrong.
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
Now you order your subset by size, in ascending order, because that is default.
Africa, Egypt, 4
Europe, France, 5
Asia, China, 7
Of the ordered subset, you ask to select the country
Egypt
France
China
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;
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