Exercise #(-2) page 2
Removing the "launched" column from grouping isn't enough to correct this mistake.
Then rows that satisfies predicate would look like this:
Thereby we have already lost correct rows for Japan and USA. We should take notice of this row for Japan:
Everything in this solution is reversed. Nevertheless let’s try to correct it by finding out the reasons of mistakes and delusions.
Correlated subquery is needed for binding of year and country. It can be used in WHERE clause (AND aa.country = s.country):
That is correct, but don’t take any effect for now, except maybe it excludes wrong row:
For advance we need to calculate minimal year among ones with maximum quantity of ships for each contry. Maximum quantity of ships is primary here. If we would filter only by minimal year we can lose right rows. That’s why we need to count quantity of ships, not a minimal year:
Take a note at predicate >= ALL which gives us maximal value of “x”. Let’s rewrite whole query with taking into account all we have said about grouping in the main query.
Is this all? Not at all. If maximum value would be gained in several different years the row for every such year will occur in the result. But we need to output the row with minimal year by the data. As it was noted above, this is the certain case when grouping is valid: all “x” values for country are equal and maximal.