selecting N record for each group - Mailing list pgsql-sql

From Ivan Sergio Borgonovo
Subject selecting N record for each group
Date
Msg-id 20080709114550.7cc8f742@dawn.webthatworks.it
Whole thread Raw
List pgsql-sql
There are many ways to achieve this:

http://rickosborne.org/blog/index.php/2008/01/07/sql-getting-top-n-rows-for-a-grouped-query/
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx

I was wondering if any of the available methods perform better on
postgresql:

The actual situation is:

create table type( id int primary key, name varchar(32)
);

create table list( id int primary key, name varchar(32), type int references type (id), inserted timestamp
);

I'd like to get the type.name, list.name of the N newest items for
a set of types (eg. where type.id in (1,2,4)).

the list of types I'm interested in is composed of 20-100 types on
10K distinct types.

list table has 1M record

N is in the order of 5-20.

I could use plpgsqql too but I'm not sure it could make any better
compared to plain sql.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it



pgsql-sql by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: Profiling postgresql queries
Next
From: Mark Stosberg
Date:
Subject: Re: Best practice for long-lived journal tables: bigint or recycling IDs?