pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan EXCEPT Be Used for To Solve This Problem?
6+ messages / 5 participants
[nested] [flat]
* Can EXCEPT Be Used for To Solve This Problem?
@ 2005-08-24 20:47 Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 3 replies; 6+ messages in thread
From: Lane Van Ingen @ 2005-08-24 20:47 UTC (permalink / raw)
To: pgsql-sql
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
solution below
works, but I think there may be a better way. Does anyone else have a better
idea?
select * from foo f1
join
(select id, update_time
from foo
except
select id, max(update_time) as update_time
from foo
group by id) f2
using (id, update_time)
order by 1, 2 desc limit 1;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Can EXCEPT Be Used for To Solve This Problem?
@ 2005-08-24 23:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lane Van Ingen <lvaningen@esncc.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-08-24 23:21 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-sql
"Lane Van Ingen" <lvaningen@esncc.com> writes:
> I want to select 2nd oldest transaction from foo (transaction 3).
Can't you just do
select * from foo order by update_time desc offset 1 limit 1
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Can EXCEPT Be Used for To Solve This Problem?
@ 2005-08-24 23:34 Michael Fuhr <mike@fuhr.org>
parent: Lane Van Ingen <lvaningen@esncc.com>
2 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-08-24 23:34 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-sql
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
> 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;
If that's not what you're after then please elaborate.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Can EXCEPT Be Used for To Solve This Problem?
@ 2005-08-24 23:52 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-08-24 23:52 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Can EXCEPT Be Used for To Solve This Problem?
@ 2005-08-25 14:47 Vivek Khera <vivek@khera.org>
parent: Lane Van Ingen <lvaningen@esncc.com>
2 siblings, 1 reply; 6+ messages in thread
From: Vivek Khera @ 2005-08-25 14:47 UTC (permalink / raw)
To: pgsql-sql
On Aug 24, 2005, at 4:47 PM, Lane Van Ingen wrote:
> I want to select 2nd oldest transaction from foo (transaction 3). The
> solution below
> works, but I think there may be a better way. Does anyone else have
> a better
> idea?
why not just select order by update_time desc limit 2 then discard
the first row you fetch?
Vivek Khera, Ph.D.
+1-301-869-4449 x806
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Can EXCEPT Be Used for To Solve This Problem?
@ 2005-08-25 15:06 Mark R. Dingee <mark.dingee@cox.net>
parent: Vivek Khera <vivek@khera.org>
0 siblings, 0 replies; 6+ messages in thread
From: Mark R. Dingee @ 2005-08-25 15:06 UTC (permalink / raw)
To: pgsql-sql
you can also do
select ... order by update_time desc offset 1 limit 1
On Thursday 25 August 2005 10:47 am, Vivek Khera wrote:
> On Aug 24, 2005, at 4:47 PM, Lane Van Ingen wrote:
> > I want to select 2nd oldest transaction from foo (transaction 3). The
> > solution below
> > works, but I think there may be a better way. Does anyone else have
> > a better
> > idea?
>
> why not just select order by update_time desc limit 2 then discard
> the first row you fetch?
>
>
> Vivek Khera, Ph.D.
> +1-301-869-4449 x806
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-25 15:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-24 20:47 Can EXCEPT Be Used for To Solve This Problem? Lane Van Ingen <lvaningen@esncc.com>
2005-08-24 23:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 23:34 ` Michael Fuhr <mike@fuhr.org>
2005-08-24 23:52 ` Michael Fuhr <mike@fuhr.org>
2005-08-25 14:47 ` Vivek Khera <vivek@khera.org>
2005-08-25 15:06 ` Mark R. Dingee <mark.dingee@cox.net>
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