From: Sean Davis <sdavis2@mail.nih.gov>
To: Lane Van Ingen <lvaningen@esncc.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Newbie
Date: Fri, 12 Aug 2005 12:02:16 -0400
Message-ID: <BF224048.C49B%sdavis2@mail.nih.gov> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIKEEECBAA.lvaningen@esncc.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sdavis2@mail.nih.gov, lvaningen@esncc.com
Subject: Re: SQL Newbie
In-Reply-To: <BF224048.C49B%sdavis2@mail.nih.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox