Received: from mailgate.generics.co.uk (mailgate.generics.co.uk [194.216.39.130]) by hub.org (8.9.3/8.9.1) with SMTP id GAA45892 for ; Fri, 23 Apr 1999 06:30:01 -0400 (EDT) (envelope-from jas1@scigen.co.uk) Received: from scigen.co.uk (scigen) by mailgate.generics.co.uk (5.0/PIPEX simple 1.26) id AA25116; Fri, 23 Apr 1999 11:28:16 +0000 Received: from scigen.co.uk (dhcp4429) by scigen.co.uk (4.1/SMI-4.1) id AA27399; Fri, 23 Apr 99 11:22:55 BST Message-Id: <37204BB5.B6CD57B9@scigen.co.uk> Date: Fri, 23 Apr 1999 10:30:14 +0000 From: Julian Scarfe Organization: Scientific Generics X-Mailer: Mozilla 4.5 [en] (WinNT; I) X-Accept-Language: en Mime-Version: 1.0 To: Tom Lane , "pgsql-sql@postgresql.org" Subject: Re: [SQL] Finding the "most recent" rows References: <17966.924794971@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > > SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC; > > might do the right thing. It *seems* to select the first row for each > value of place. I've never seen a spec for this feature, however, so > I'm not sure if it's reliable or not... Works a treat when I try it on the "real" database (using 6.4.2). It seems very efficient. I didn't realise that "DISTINCT ON" worked that way with an "ORDER BY". I must say I'm overwhelmed by the variety of constructive responses to my request. Thank you all very much. It beats the hell out of sitting as no 17 in a premium rate telephone queue for support from a vendor of a "commercial" product. -- Julian Scarfe