Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
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
);