X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1FE12D7A19 for ; Wed, 24 Aug 2005 17:50:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63763-02 for ; Wed, 24 Aug 2005 20:50:44 +0000 (GMT) Received: from TWMAIL.ESNCC.COM (static-66-173-159-28.t1.cavtel.net [66.173.159.28]) by svr1.postgresql.org (Postfix) with ESMTP id 0ABA9D7A16 for ; Wed, 24 Aug 2005 17:50:42 -0300 (ADT) Received: from spawar2i8uvlb9 ([150.125.117.63]) by TWMAIL.ESNCC.COM with Microsoft SMTPSVC(5.0.2195.6713); Wed, 24 Aug 2005 16:50:43 -0400 From: "Lane Van Ingen" To: Subject: Can EXCEPT Be Used for To Solve This Problem? Date: Wed, 24 Aug 2005 16:47:16 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 X-OriginalArrivalTime: 24 Aug 2005 20:50:43.0703 (UTC) FILETIME=[7EBE1870:01C5A8ED] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.071 required=5 tests=[AWL=0.021, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/232 X-Sequence-Number: 22460 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;