[Please don't top post as it makes the discussion more difficult to
follow.]
On Aug 27, 2007, at 12:34 , Joel Richard wrote:
On Aug 27, 2007, at 12:51 PM, D'Arcy J.M. Cain wrote:
On Mon, 27 Aug 2007 18:36:47 +0200
Claudia Kosny <[EMAIL PROTECTED]> wrote:
I have a list of events that take place in a certain city at a
certain
date. Now I would like to have the first two (ordered by date)
events
for each city.
Is there a way to do this with one query?
I am using PostgreSQL 7.4.
I believe you are looking for the LIMIT keyword. Check the docs
on the
web site.
That's what I thought at first, but this is not the case. She's
looking for the first two dates in -each- city in the table. I
initially thought that this could be accomplished with GROUP BY and
LIMIT, but GROUP BY can only give you the first date for each city,
not the first two dates for each city.
So far, I haven't come up with any clever ideas. I'm not sure this
can be done in SQL.
--Joel
I can't think of an easy way to do it, but certainly can do it in SQL
using correlated subqueries, something like
select city, event, event_date, (
select count(event)
from events i
where i.city = o.city
and i.event_date < o.event_date
and event_date > current_date -- make sure they're future
events
) as nearness_rank
from events o
where event_date > current_date -- make sure they're future events
having nearness_rank <= 1;
Note that this can potentially show more than 2 events if the most
recent upcoming events "tie" (have the same event_date).
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
