From: "Nick Stone" <nick@harelane.com>
To: "'Lane Van Ingen'" <lvaningen@esncc.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: SQL Newbie
Date: Fri, 12 Aug 2005 18:57:34 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcWfUQhHaI01fX5LRC2yEi4AdhWBJwAFdbNg
In-Reply-To: <EKEMKEFLOMKDDLIALABIKEEECBAA.lvaningen@esncc.com>
Message-Id: <20050812175529.82D7F24F3B9@smtp.nildram.co.uk>

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

Nick

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Lane Van Ingen
Sent: 12 August 2005 16:09
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL Newbie

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster