pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Was: fetch first rows of grouped data
2+ messages / 2 participants
[nested] [flat]

* Was: fetch first rows of grouped data
@ 2007-08-28 16:05 Guy Fraser <guy@incentre.net>
  2007-08-28 17:38 ` Re: Was: fetch first rows of grouped data Sebastian Ritter <ritter.sebastian@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Guy Fraser @ 2007-08-28 16:05 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


I think I may have come up with a possible solution.

Create a selection that produces a unique identifier for each city
ordered by date then use array_accum to collect the unique identifiers 
for each city, then match the first two elements of the array with the 
identifiers.

For instance if you had a table :

CREATE TABLE crazy_talk (
 ct_id bigserial primary key,
 ct_city text,
 ct_date date,
 ct_data text
) ;

Then you could use :

SELECT
 ct_id , 
 ct_city , 
 ct_date , 
 ct_data 
FROM 
 crazy_talk , 
 (SELECT 
  ct_city AS city, 
  array_accum(ct_id) as match 
 FROM 
  crazy_talk 
 ORDER BY 
  ct_city , 
  ct_date 
 GROUP BY 
  ct_city ) AS data_set 
WHERE 
 ct_city = city AND
 ct_id IN (match[0],match[1])
ORDER BY
 ct_city ,
 ct_date
;

I hope this helps, I did not try it, but I think it should work.

PS if you don't have array_accum here it is :

CREATE AGGREGATE array_accum (
    BASETYPE = anyelement,
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Was: fetch first rows of grouped data
  2007-08-28 16:05 Was: fetch first rows of grouped data Guy Fraser <guy@incentre.net>
@ 2007-08-28 17:38 ` Sebastian Ritter <ritter.sebastian@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Sebastian Ritter @ 2007-08-28 17:38 UTC (permalink / raw)
  To: Guy Fraser <guy@incentre.net>

Sorry my last post isnt complete.

This would have to be part of a sub select. It would be more lilke:

SELECT * from cities LEFT OUTER JOIN (SELECT c.id as city_id, event.id FROM
cities c LEFT OUTER JOIN events e ON (c.id = e.city_id) WHERE c.id =
cities.id ORDER BY e.date DESC LIMIT 2) as x ON (cities.id = x.city_id)

I think that would work.

Seb

On 8/28/07, Sebastian Ritter <ritter.sebastian@gmail.com> wrote:
>
> Hi There,
>
> You can do something like :
>
> SELECT * FROM cities c LEFT OUTER JOIN events e ON (c.id =e.city_id) ORDER
> BY e.date DESC LIMIT 2
>
> The left outer join here would ensure that cities with no events are also
> added to your result set.
>
> Seb
>
> On 8/28/07, Guy Fraser <guy@incentre.net> wrote:
> >
> >         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
> >
> >
> > I think I may have come up with a possible solution.
> >
> > Create a selection that produces a unique identifier for each city
> > ordered by date then use array_accum to collect the unique identifiers
> > for each city, then match the first two elements of the array with the
> > identifiers.
> >
> > For instance if you had a table :
> >
> > CREATE TABLE crazy_talk (
> > ct_id bigserial primary key,
> > ct_city text,
> > ct_date date,
> > ct_data text
> > ) ;
> >
> > Then you could use :
> >
> > SELECT
> > ct_id ,
> > ct_city ,
> > ct_date ,
> > ct_data
> > FROM
> > crazy_talk ,
> > (SELECT
> >   ct_city AS city,
> >   array_accum(ct_id) as match
> > FROM
> >   crazy_talk
> > ORDER BY
> >   ct_city ,
> >   ct_date
> > GROUP BY
> >   ct_city ) AS data_set
> > WHERE
> > ct_city = city AND
> > ct_id IN (match[0],match[1])
> > ORDER BY
> > ct_city ,
> > ct_date
> > ;
> >
> > I hope this helps, I did not try it, but I think it should work.
> >
> > PS if you don't have array_accum here it is :
> >
> > CREATE AGGREGATE array_accum (
> >     BASETYPE = anyelement,
> >     SFUNC = array_append,
> >     STYPE = anyarray,
> >     INITCOND = '{}'
> > );
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-08-28 17:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-28 16:05 Was: fetch first rows of grouped data Guy Fraser <guy@incentre.net>
2007-08-28 17:38 ` Sebastian Ritter <ritter.sebastian@gmail.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