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