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 KAA63519 for ; Thu, 22 Apr 1999 10:14:32 -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 AA19742; Thu, 22 Apr 1999 15:12:47 +0000 Received: from scigen.co.uk (dhcp4429) by scigen.co.uk (4.1/SMI-4.1) id AA16813; Thu, 22 Apr 99 15:07:27 BST Message-Id: <371F2ED6.29FD9251@scigen.co.uk> Date: Thu, 22 Apr 1999 14:14:46 +0000 From: Julian Scarfe Organization: Scientific Generics X-Mailer: Mozilla 4.5 [en] (WinNT; I) X-Accept-Language: en Mime-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Finding the "most recent" rows Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a table (representing a set of observations) with datetime fields and a non-unique place field. e.g. create table obs ( the_time datetime, the_place char(8), ...other fields... ) 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. e.g. given a table such as: the_time the_place ... 0910 London 1130 London 0910 Paris 0930 London 0840 Paris 1020 London 0740 Paris I'd like to select: 1130 London 0910 Paris Most of my attempts at this (as an SQL novice) feel very clumsy and inefficient. Is there an efficient way of doing this in SQL? -- Julian Scarfe