01:44

# Exercise #(-2) page 4

Solution 3.6.4.

Using HAVING clause.

Console
Execute
`SELECT country, QTY, MIN(launched)FROM (SELECT country, launched, COUNT(name) QTY FROM Classes c JOIN  Ships s ON c.class = s.class GROUP BY country,launched HAVING COUNT(name) = (SELECT MAX(qty)  FROM (SELECT country,launched,COUNT(name) qty FROM Classes c1 JOIN  Ships s1 ON c1.class = s1.class WHERE country = c.country GROUP BY country,launched )e ) )TGROUP BY t.qty, t.country;`

If the subquery of the FROM clause rows {country, launch year, quantity of ships} are determined first. Then HAVING clause filters only rows with quantity of ships that is equal to maximum quantity of ships for this country. Take note that subquery in this predicate is correlated.

`WHERE country = c.country`

That is why MAX(qty) relates to certain country from the main query and is not a global maximum. At last, it determines minimal year for each combination {country, maximum quantity of ships}.

Can we move to the next task? No we can’t. All of considered variants of solutions contain one type of mistake. We offer you to find it by your own.

If you can’t make it you are free to look at Q&A.

To solve a problem on SQL-EX.RU

 Pages 1 2 3 4
Tags
The book was updated
month ago