Re: Was: fetch first rows of grouped data - Mailing list pgsql-sql

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