Date: Wed, 24 Aug 2005 17:52:23 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Lane Van Ingen <lvaningen@esncc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can EXCEPT Be Used for To Solve This Problem?
Message-ID: <20050824235223.GA19076@winnie.fuhr.org>
References: <EKEMKEFLOMKDDLIALABIMEMGCBAA.lvaningen@esncc.com>
	<20050824233449.GA12396@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050824233449.GA12396@winnie.fuhr.org>

On Wed, Aug 24, 2005 at 05:34:49PM -0600, Michael Fuhr wrote:
> On Wed, Aug 24, 2005 at 04:47:16PM -0400, Lane Van Ingen wrote:
> > Given the following data in a table named 'foo' :
> >      id   update_time           description
> >      2    2005-08-24 00:10:00   transaction1
> >      2    2005-08-24 00:22:00   transaction2
> >      2    2005-08-24 00:34:00   transaction3
> >      2    2005-08-24 00:58:00   transaction4
> > 
> > I want to select 2nd oldest transaction from foo (transaction 3). The

I just noticed that transaction3 isn't the 2nd oldest, it's the 3rd
oldest and the 2nd newest.  What are you really trying to do?

> > solution below
> > works, but I think there may be a better way. Does anyone else have a better
> > idea?
> 
> Do you want the 2nd oldest transaction from the entire table?  If
> so then the following should work:
> 
> SELECT *
> FROM foo
> ORDER BY update_time
> OFFSET 1
> LIMIT 1;

Flaw: this query assumes that the 2nd record in the ordered result
set is the 2nd oldest transaction, which isn't necessarily true.
If the update_time values aren't unique, then the 2nd record could
have the oldest time and not the 2nd oldest time.  Is that why you
were using EXCEPT?  To exclude all instances of the oldest time?

-- 
Michael Fuhr