Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.1) with ESMTP id WAA03594 for ; Thu, 22 Apr 1999 22:58:52 -0400 (EDT) (envelope-from chairudin@prima.net.id) Received: from ns1.prima.net.id ([202.57.0.8]) by trends.net (8.8.8/8.8.8) with ESMTP id WAA24171 for ; Thu, 22 Apr 1999 22:02:39 -0400 (EDT) Received: from prima.net.id (mail.prima.net.id [202.57.0.8]) by ns1.prima.net.id (8.8.4/8.7.2) with ESMTP id PAA10064; Fri, 23 Apr 1999 15:30:05 +0700 (GMT) Sender: root@ns1.prima.net.id Message-ID: <37202F8D.B9479A67@prima.net.id> Date: Fri, 23 Apr 1999 01:30:05 -0700 From: Chairudin Sentosa Reply-To: chairudin@prima.net.id Organization: PT. Khasanah Timur Indonesia X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.5.1 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: Brook Milligan CC: jas1@scigen.co.uk, pgsql-sql@postgreSQL.org Subject: Re: [SQL] Finding the "most recent" rows References: <371F2ED6.29FD9251@scigen.co.uk> <199904221851.MAA10263@trillium.nmsu.edu> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Your script doesn't show how to get the "most recent" rows. The output from the 1st SQL is : id|customer|order_no --+--------+-------- 1| 1| 1 2| 1| 2 3| 1| 3 4| 2| 1 5| 2| 2 6| 3| 1 The output from the 2nd SQL is: id|customer|order_no --+--------+-------- 2| 1| 2 5| 2| 2 6| 3| 1 What are you trying to show here? I think you could just select the highest id, which means the latest data input. Regards, Chai Brook Milligan wrote: > 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;