| From: | "Lane Van Ingen" <lvaningen(at)esncc(dot)com> |
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Can EXCEPT Be Used for To Solve This Problem? |
| Date: | 2005-08-24 20:47:16 |
| Message-ID: | EKEMKEFLOMKDDLIALABIMEMGCBAA.lvaningen@esncc.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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;
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2005-08-24 23:21:03 | Re: Can EXCEPT Be Used for To Solve This Problem? |
| Previous Message | Scott Marlowe | 2005-08-24 20:46:06 | Re: how to do a select * and decrypt a column at the same |