Re: SQL Newbie

On 8/12/05 11:09 AM, "Lane Van Ingen" <lvaningen(at)esncc(dot)com> wrote:

> It seems to me that I should be able to do this, but after 5 hrs of trying,
> I
> can't figure this one out.
>
> I could do this in two queries, but seems like I should be able to do this
> in
> one. What I am trying to do:
> Find the highest speed at which each interface of a router has run over
> time.
>
> I have three tables, two of which (interface, speed_history) are being used
> in
> this query (primary / foreign key fields noted as PK / FK):
>
> router -> 1:M -> interface -> 1:M -> speed_history
> ------------------- --------------------------- --------------------------
> -
> router_no (int2) PK interface_id (int4) PK interface_id (int4) PK
> name (varchar) router_no (int2) FK updated_time (timestamp)
> PK
> link_description (varchar) speed (int4)
>
> Data in speed history looks like this:
> interface_id updated_time speed
> 1 2005-08-11 08:10:23 450112
> 1 2005-08-11 10:53:34 501120 <---
> 1 2005-08-11 10:58:11 450112
> 2 2005-08-11 08:10:23 450112 <---
> 2 2005-08-11 11:00:44 350234
> 3 2005-08-11 08:10:23 450112 <---
> The rows of speed_history I want back are marked above with ' <--- '.
>
> Query results should look like:
> interface.interface_id
> interface.link_description
> speed_history.updated_time
> speed_history.speed

What about (untested):

SELECT a.interface_id,
a.link_description,
c.updated_time,
c.speed
FROM
interface a,
(select interface_id,max(speed) as speed
from speed_history,interface group by interface_id) as b,
speed_history c
WHERE
b.interface_id=a.interface_id AND
c.speed=b.speed;

Sean