Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 09F499F9846 for ; Tue, 28 Aug 2007 14:38:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 93953-02 for ; Tue, 28 Aug 2007 14:38:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from bach.incentre.net (bach.incentre.net [198.161.96.4]) by postgresql.org (Postfix) with ESMTP id F03EB9F97CC for ; Tue, 28 Aug 2007 14:38:24 -0300 (ADT) Received: from sigurd.incentre.net (sigurd.incentre.net [206.75.213.195]) by bach.incentre.net (8.12.9p2/8.12.9) with ESMTP id l7SHcP5j010995 for ; Tue, 28 Aug 2007 11:38:25 -0600 (MDT) (envelope-from guy@incentre.net) Resent-Date: Tue, 28 Aug 2007 11:38:25 -0600 (MDT) Resent-Message-Id: <200708281738.l7SHcP5j010995@bach.incentre.net> Received: by bach.incentre.net (mbox gfraser) (with Cubic Circle's cucipop (v1.31 1998/05/13) Tue Aug 28 10:33:21 2007) X-From_: ritter.sebastian@gmail.com Tue Aug 28 10:31:47 2007 Received: from psmtp.com (exprod5mx232.postini.com [64.18.0.118]) by bach.incentre.net (8.12.9p2/8.12.9) with SMTP id l7SGVk5j057378 for ; Tue, 28 Aug 2007 10:31:46 -0600 (MDT) (envelope-from ritter.sebastian@gmail.com) Received: from source ([64.233.162.229]) by exprod5mx232.postini.com ([64.18.4.10]) with SMTP; Tue, 28 Aug 2007 09:31:44 PDT Received: by nz-out-0506.google.com with SMTP id i28so1242035nzi for ; Tue, 28 Aug 2007 09:31:43 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=FOQn3dwAJLmZedQhxlvHkumW7XyUTcA6c/Gp+zqLJsJ0FylC3gE4B4kWORaitIoisSAqYXJEt1kankf9Y5BOnqzVO6TJSuLYuk7HV3dzhZMw5zZRcsta/Eeiv3P4iQRwrboSqNzfpYyZvvKbl/y45r+3f0O+ZXlVRqCRo/QpYBs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=nP+W5Jyi2Qg8NyR+qLzcFKSADvOEE4txEFmMkB9/SKVdhCyGZVLIufx9UEg6ot7UTx7JCr4Emk7Zbi2tr/S4Lta+kU2+KZYiqwj/VtcFSduHeyoH2ZUpRYEqd8FYqCDJmu3BDVTX2val80qa9319DIbQRstaq0xCJ5imdtNm2U4= Received: by 10.141.3.10 with SMTP id f10mr2039129rvi.1188318703234; Tue, 28 Aug 2007 09:31:43 -0700 (PDT) Received: by 10.141.40.8 with HTTP; Tue, 28 Aug 2007 09:31:43 -0700 (PDT) Message-ID: <99b656cb0708280931h19ca997dw68ed251beb2a99ff@mail.gmail.com> From: "Sebastian Ritter" To: "Guy Fraser" In-Reply-To: <99b656cb0708280923p171fb6eg9a2458dc08f7e33b@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29666_24716780.1188318703208" References: <1188317103.8070.34.camel@sigurd.incentre.net> <99b656cb0708280923p171fb6eg9a2458dc08f7e33b@mail.gmail.com> X-pstn-levels: (S:86.61848/99.90000 R:95.9108 P:95.9108 M:97.0282 C:98.6951 ) X-pstn-settings: 5 (2.0000:2.0000) s gt3 gt2 gt1 r p m c X-pstn-addresses: from [720/29] Subject: Re: Was: fetch first rows of grouped data Resent-From: Guy Fraser Resent-To: pgsql-sql@postgresql.org Organization: The Internet Centre Date: Tue, 28 Aug 2007 11:38:22 -0600 X-Mailer: Evolution 2.6.3 (2.6.3-2.fc5) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/248 X-Sequence-Number: 29186 ------=_Part_29666_24716780.1188318703208 Content-Type: text/plain; charset=ISO-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 7bit 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 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 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 > > > > ------=_Part_29666_24716780.1188318703208 Content-Type: text/html; charset=ISO-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 7bit 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


------=_Part_29666_24716780.1188318703208--