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

Instruction

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

SELECT
  *
FROM Trip MainTrip
WHERE Price >= ALL (
  SELECT
    SubTrip.Price
  FROM Trip SubTrip
  WHERE MainTrip.CityID = SubTrip.CityID
);

The above query looks for the most expensive trips to each city. The agency only chooses trips with a price greater than or equal to all trip prices in a given city.

Exercise

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

Stuck? Here's a hint!

Type:

SELECT
  *
FROM HikingTrip MainHikingTrip
WHERE MainHikingTrip.Length >= ALL (
  SELECT
    SubHikingTrip.Length
  FROM HikingTrip SubHikingTrip
  WHERE MainHikingTrip.MountainID = SubHikingTrip.MountainID
);