pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfetch first rows of grouped data
9+ messages / 5 participants
[nested] [flat]
* fetch first rows of grouped data
@ 2007-08-27 16:36 Claudia Kosny <ckosny@gmx.net>
2007-08-27 16:51 ` Re: fetch first rows of grouped data D'Arcy J.M. Cain <darcy@druid.net>
2007-08-28 09:14 ` Re: fetch first rows of grouped data hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 9+ messages in thread
From: Claudia Kosny @ 2007-08-27 16:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
@ 2007-08-27 16:51 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-08-27 17:34 ` Re: fetch first rows of grouped data Joel Richard <postgres@joelrichard.com>
1 sibling, 1 reply; 9+ messages in thread
From: D'Arcy J.M. Cain @ 2007-08-27 16:51 UTC (permalink / raw)
To: Claudia Kosny <ckosny@gmx.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-27 16:51 ` Re: fetch first rows of grouped data D'Arcy J.M. Cain <darcy@druid.net>
@ 2007-08-27 17:34 ` Joel Richard <postgres@joelrichard.com>
2007-08-27 18:12 ` Re: fetch first rows of grouped data Michael Glaesemann <grzm@seespotcode.net>
2007-08-27 18:15 ` Re: fetch first rows of grouped data D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 2 replies; 9+ messages in thread
From: Joel Richard @ 2007-08-27 17:34 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; Claudia Kosny <ckosny@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-27 16:51 ` Re: fetch first rows of grouped data D'Arcy J.M. Cain <darcy@druid.net>
2007-08-27 17:34 ` Re: fetch first rows of grouped data Joel Richard <postgres@joelrichard.com>
@ 2007-08-27 18:12 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-27 18:20 ` Re: fetch first rows of grouped data Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2007-08-27 18:12 UTC (permalink / raw)
To: Joel Richard <postgres@joelrichard.com>; +Cc: D'Arcy J.M. Cain <darcy@druid.net>; Claudia Kosny <ckosny@gmx.net>; pgsql-sql
[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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-27 16:51 ` Re: fetch first rows of grouped data D'Arcy J.M. Cain <darcy@druid.net>
2007-08-27 17:34 ` Re: fetch first rows of grouped data Joel Richard <postgres@joelrichard.com>
2007-08-27 18:12 ` Re: fetch first rows of grouped data Michael Glaesemann <grzm@seespotcode.net>
@ 2007-08-27 18:20 ` Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Glaesemann @ 2007-08-27 18:20 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Joel Richard <postgres@joelrichard.com>; D'Arcy J.M. Cain <darcy@druid.net>; Claudia Kosny <ckosny@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-27 16:51 ` Re: fetch first rows of grouped data D'Arcy J.M. Cain <darcy@druid.net>
2007-08-27 17:34 ` Re: fetch first rows of grouped data Joel Richard <postgres@joelrichard.com>
@ 2007-08-27 18:15 ` D'Arcy J.M. Cain <darcy@druid.net>
1 sibling, 0 replies; 9+ messages in thread
From: D'Arcy J.M. Cain @ 2007-08-27 18:15 UTC (permalink / raw)
To: Joel Richard <postgres@joelrichard.com>; +Cc: Claudia Kosny <ckosny@gmx.net>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
@ 2007-08-28 09:14 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-28 13:00 ` Re: fetch first rows of grouped data Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 1 reply; 9+ messages in thread
From: hubert depesz lubaczewski @ 2007-08-28 09:14 UTC (permalink / raw)
To: Claudia Kosny <ckosny@gmx.net>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-28 09:14 ` Re: fetch first rows of grouped data hubert depesz lubaczewski <depesz@depesz.com>
@ 2007-08-28 13:00 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-28 13:13 ` Re: fetch first rows of grouped data hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2007-08-28 13:00 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Claudia Kosny <ckosny@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: fetch first rows of grouped data
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-28 09:14 ` Re: fetch first rows of grouped data hubert depesz lubaczewski <depesz@depesz.com>
2007-08-28 13:00 ` Re: fetch first rows of grouped data Michael Glaesemann <grzm@seespotcode.net>
@ 2007-08-28 13:13 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 9+ messages in thread
From: hubert depesz lubaczewski @ 2007-08-28 13:13 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Claudia Kosny <ckosny@gmx.net>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-08-28 13:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-27 16:36 fetch first rows of grouped data Claudia Kosny <ckosny@gmx.net>
2007-08-27 16:51 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-08-27 17:34 ` Joel Richard <postgres@joelrichard.com>
2007-08-27 18:12 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-27 18:20 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-27 18:15 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-08-28 09:14 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-28 13:00 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-28 13:13 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox