Re: Subselect Question

Alex P wrote:
> Hi,
>
> when creating a query with a subselect
>
> SELECT name, (SELECT max(pop) FROM cities WHERE cities.state =
> states.name) AS max_pop
>    FROM states;
>
> then it is not possible to sort after max_pop or use max_pop in a
> function or a CASE.

Here max_pop is naming the whole subselect. How about something like:

SELECT name, max_pop
FROM
  states,
  (SELECT state AS target_state, max(pop) AS max_pop FROM cities) AS pops
WHERE
  states.name = pops.target_state
;

--
   Richard Huxton
   Archonet Ltd