User-Agent: Microsoft-Entourage/10.1.4.030702.0
Date: Fri, 12 Aug 2005 12:02:16 -0400
Subject: Re: SQL Newbie
From: Sean Davis <sdavis2@mail.nih.gov>
To: Lane Van Ingen <lvaningen@esncc.com>, <pgsql-sql@postgresql.org>
Message-ID: <BF224048.C49B%sdavis2@mail.nih.gov>
In-Reply-To: <EKEMKEFLOMKDDLIALABIKEEECBAA.lvaningen@esncc.com>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit

On 8/12/05 11:09 AM, "Lane Van Ingen" <lvaningen@esncc.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