Re: SQL Newbie

On Fri, Aug 12, 2005 at 18:57:34 +0100, Nick Stone <nick@harelane.com> wrote:
> Hope this helps
> 
> SELECT
>     *
> FROM
>     speed_history as outside etc..
> WHERE
>     (speed = (
>         SELECT
>             speed
>         FROM
>             speed_history as inside etc..
>         WHERE
>             (outside.interface = inside.interface)
>         LIMIT 1
>         ORDER BY
>             speed DESC
>         )
>     )
> 
> Hopefully you get the idea - basically it's a corelated sub-query - very
> useful
> 

Note that you want to order by interface DESC, speed DESC in the subselect
and have an index on (interface, speed) for this to really be efficient.
I think 8.1 might be smart enough to use an index on (interface, speed)
in the above case, but currently released versions won't.