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?
Date: Wed, 24 Aug 2005 17:52:23 -0600
Message-ID: <20050824235223.GA19076@winnie.fuhr.org> (raw)
In-Reply-To: <20050824233449.GA12396@winnie.fuhr.org>
References: <EKEMKEFLOMKDDLIALABIMEMGCBAA.lvaningen@esncc.com>
<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
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: mike@fuhr.org, lvaningen@esncc.com
Subject: Re: Can EXCEPT Be Used for To Solve This Problem?
In-Reply-To: <20050824235223.GA19076@winnie.fuhr.org>
* 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