Thread: fetch first rows of grouped data
Hi there 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. Thanks for any tips. Claudia
On Mon, 27 Aug 2007 18:36:47 +0200 Claudia Kosny <ckosny@gmx.net> 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. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
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 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 <ckosny@gmx.net> 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. > > -- > D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three > wolves > http://www.druid.net/darcy/ | and a sheep voting on > +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
[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 <ckosny@gmx.net> 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
On Mon, 27 Aug 2007 13:34:09 -0400 Joel Richard <postgres@joelrichard.com> wrote: > 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. Yes, you are correct. My mistake. > So far, I haven't come up with any clever ideas. I'm not sure this > can be done in SQL. Well, I can think of an SQL only method involving a temporary table but it would not be a single query: - Select into temp table where date is max.- Select from temp table union with main table where date is max and is not in temp table. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Aug 27, 2007, at 13:12 , Michael Glaesemann wrote: > 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 This should be i.event_date > current_date to be explicit. > ) as nearness_rank > from events o > where event_date > current_date -- make sure they're future events > having nearness_rank <= 1; Michael Glaesemann grzm seespotcode net
On Mon, Aug 27, 2007 at 06:36:47PM +0200, Claudia Kosny wrote: > Is there a way to do this with one query? > I am using PostgreSQL 7.4. you can try to use "distinct on". depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
On Aug 28, 2007, at 4:14 , hubert depesz lubaczewski wrote: > On Mon, Aug 27, 2007 at 06:36:47PM +0200, Claudia Kosny wrote: >> Is there a way to do this with one query? >> I am using PostgreSQL 7.4. > > you can try to use "distinct on". I considered that as well, but couldn't think of a way to return more than one row per "distinct on condition". I'd be interested to hear if you've thought of a way to use DISTINCT ON in this situation. Michael Glaesemann grzm seespotcode net
On Tue, Aug 28, 2007 at 08:00:42AM -0500, Michael Glaesemann wrote: > >you can try to use "distinct on". > I considered that as well, but couldn't think of a way to return more you're right and i was wrong. i simply missed the word "two" in original question. sorry. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)