Обсуждение: Was: 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
wouldlike 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 :
SELECTct_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
GROUPBY ct_city ) AS data_set
WHERE ct_city = city ANDct_id IN (match[0],match[1])
ORDER BYct_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 = '{}'
);
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
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.
SebOn 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