From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Subject: Was: fetch first rows of grouped data
Date: Tue, 28 Aug 2007 10:05:03 -0600
Message-ID: <1188317103.8070.34.camel@sigurd.incentre.net> (raw)
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 = '{}'
);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guy@incentre.net
Subject: Re: Was: fetch first rows of grouped data
In-Reply-To: <1188317103.8070.34.camel@sigurd.incentre.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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