Received: from trillium.nmsu.edu (trillium.NMSU.Edu [128.123.5.15]) by hub.org (8.9.3/8.9.1) with ESMTP id OAA99817 for ; Thu, 22 Apr 1999 14:52:06 -0400 (EDT) (envelope-from brook@trillium.NMSU.Edu) Received: (from brook@localhost) by trillium.nmsu.edu (8.8.8/8.8.8) id MAA10263; Thu, 22 Apr 1999 12:51:29 -0600 (MDT) Date: Thu, 22 Apr 1999 12:51:29 -0600 (MDT) Message-Id: <199904221851.MAA10263@trillium.nmsu.edu> X-Authentication-Warning: trillium.nmsu.edu: brook set sender to brook@trillium.nmsu.edu using -f From: Brook Milligan To: jas1@scigen.co.uk CC: pgsql-sql@postgreSQL.org In-reply-to: <371F2ED6.29FD9251@scigen.co.uk> (message from Julian Scarfe on Thu, 22 Apr 1999 14:14:46 +0000) Subject: Re: [SQL] Finding the "most recent" rows References: <371F2ED6.29FD9251@scigen.co.uk> I'd like an efficient way to pull out the most recent row (i.e. highest datatime) belonging to *each* of a number of places selected by a simple query. The "Practical SQL Handbook" has a description of exactly what you are looking for (don't have it handy or I'd give you the page number). They discuss two ways to do it. One uses the HAVING clause with GROUP BY (I think that is the section of the book to look in), but I don't think psql supports this. The other way uses a subselect which is supported by psql. The script at the bottom illustrates some of the ideas. Cheers, Brook =========================================================================== /* -*- C -*- * recent.sql */ /* * find the most recent entry (order) for each group (customer) */ -- create tables drop sequence invoices_id_seq; drop table invoices; create table invoices ( id serial, customer int, order_no int, unique (customer, order_no) ); insert into invoices (customer, order_no) values (1, 1); insert into invoices (customer, order_no) values (1, 2); insert into invoices (customer, order_no) values (1, 3); insert into invoices (customer, order_no) values (2, 1); insert into invoices (customer, order_no) values (2, 2); insert into invoices (customer, order_no) values (3, 1); select * from invoices order by customer, order_no; select * from invoices r where order_no = (select max (order_no) from invoices r2 where r.customer = r2.customer and order_no < 3) order by r.customer, r.order_no;