Date: Fri, 12 Aug 2005 14:39:23 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Nick Stone <nick@harelane.com>
Cc: 'Lane Van Ingen' <lvaningen@esncc.com>, pgsql-sql@postgresql.org
Subject: Re: SQL Newbie
Message-ID: <20050812193923.GA10364@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Nick Stone <nick@harelane.com>,
	'Lane Van Ingen' <lvaningen@esncc.com>, pgsql-sql@postgresql.org
References: <EKEMKEFLOMKDDLIALABIKEEECBAA.lvaningen@esncc.com>
	<20050812175529.82D7F24F3B9@smtp.nildram.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050812175529.82D7F24F3B9@smtp.nildram.co.uk>
User-Agent: Mutt/1.5.6i

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.