Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E1A4E475C60
	for <pgsql-sql@postgresql.org>; Fri, 30 Aug 2002 15:57:10 -0400 (EDT)
Received: from beamish.nsd.ca (unknown [205.150.156.194])
	by postgresql.org (Postfix) with ESMTP id 40D67475C45
	for <pgsql-sql@postgresql.org>; Fri, 30 Aug 2002 15:57:10 -0400 (EDT)
Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id PAA12915;
	Fri, 30 Aug 2002 15:57:09 -0400
X-Authentication-Warning: beamish.nsd.ca: smap set sender to <jllachan@nsd.ca>
	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 xma012913; Fri, 30 Aug 02 15:57:09 -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 PAA25516;
	Fri, 30 Aug 2002 15:57:08 -0400
Message-ID: <3D6FCEFA.361363F1@nsd.ca>
Date: Fri, 30 Aug 2002 16:00:58 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Marco Muratori <marco.muratori@emaze.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: query problem
References: <Pine.LNX.4.30.0208301824330.25859-100000@marco.intranet>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/430
X-Sequence-Number: 9276

How about:

select city, min(date) from thetable where date > '2002-07-19
15:39:15+00' group by city;

JLL


Marco Muratori wrote:
> 
> Hi
> suppose I have the following situation:
> 
>   city            date
> ---------+-----------------------
> London   | 2002-08-08 07:05:16+00
> London   | 2002-07-30 13:08:22+00
> London   | 2002-07-30 07:39:15+00
> London   | 2002-07-29 17:51:47+00
> London   | 2002-07-29 17:45:49+00
> London   | 2002-07-29 17:45:47+00
> Paris    | 2002-04-08 15:04:28+00
> Paris    | 2002-03-29 17:22:18+00
> Paris    | 2002-02-15 12:50:32+00
> Paris    | 2002-01-22 11:40:22+00
> Paris    | 2002-01-07 17:41:23+00
> Paris    | 2001-11-12 16:37:37+00
> Paris    | 2001-11-05 15:28:23+00
> Paris    | 2001-11-05 08:21:19+00
> Oslo     | 2002-07-19 15:42:20+00
> Oslo     | 2002-07-19 15:42:18+00
> Oslo     | 2002-07-18 10:03:58+00
> Oslo     | 2002-07-18 08:56:30+00
> Oslo     | 2002-07-17 17:17:27+00
> Oslo     | 2002-07-17 16:11:38+00
> 
> For each city I have a couple of dates in DESC order.
> For each city i need to get the first record which date
> comes after a given date. If the given date was for example
> "2002-07-19 15:39:15+00", I would get the following
> records:
> 
> London   | 2002-07-29 17:45:47+00
> Oslo     | 2002-07-19 15:42:18+00
> 
> 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;"?
> Thanks.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org