Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A2D79475A95 for ; Fri, 30 Aug 2002 15:59:13 -0400 (EDT) Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 897E9475D6A for ; Fri, 30 Aug 2002 15:59:11 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id PAA12958; Fri, 30 Aug 2002 15:59:09 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma012952; Fri, 30 Aug 02 15:59:03 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id PAA25545; Fri, 30 Aug 2002 15:59:03 -0400 Message-ID: <3D6FCF6D.8E072AE0@nsd.ca> Date: Fri, 30 Aug 2002 16:02:53 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686) X-Accept-Language: en MIME-Version: 1.0 To: josh@agliodbs.com Cc: Marco Muratori , pgsql-sql@postgresql.org Subject: Re: query problem References: <200208291345.25680.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/431 X-Sequence-Number: 9277 I think you meant min(date)... Josh Berkus wrote: > > Marco, > > > Is there a way to obtain this records by performing one > > single query and not by making for each city something like > > "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19 > > 15:39:15+00' ORDER BY date ASC LIMIT 1;"? > > Close. Try: > > SELECT city, MAX("date") as last_date > FROM table > WHERE "date" > $date > GROUP BY city > ORDER BY city > > Though as an aggregate query, this will be slow on large tables. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org