Thread: Can EXCEPT Be Used for To Solve This Problem?
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 fromfoo group by id) f2 using (id, update_time) order by 1, 2 desc limit 1;
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
"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
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
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
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