Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
17. The operator ALL in correlated subqueries
Other subqueries

Instruction

Good. Still remember the operator ALL? Let's use it in a correlated subquery.

SELECT
  * 
FROM trip main_trip 
WHERE price >= ALL (
  SELECT price 
  FROM trip sub_trip 
  WHERE main_trip.city_id = sub_trip.city_id
);

The above query looks for all trips which are the most expensive of all trips to that specific city. The instructions only choose trips with their price equal to or greater than all trip prices in the specific city.

Exercise

Select the hiking trip with the longest distance (column length) for every mountain.

Stuck? Here's a hint!

Type:

SELECT
  * 
FROM hiking_trip main_hiking_trip 
WHERE length >= ALL (
  SELECT length
  FROM hiking_trip sub_hiking_trip
  WHERE main_hiking_trip.mountain_id = sub_hiking_trip.mountain_id);