Обсуждение: query problem

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.

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 BerkusAglio Database SolutionsSan Francisco

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

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