selecting N record for each group

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-sql(at)postgresql(dot)org
Subject: selecting N record for each group
Date: 2008-07-09 09:45:50
Message-ID: 20080709114550.7cc8f742@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mark Stosberg 2008-07-09 12:49:51 Re: Best practice for long-lived journal tables: bigint or recycling IDs?
Previous Message A. Kretschmer 2008-07-09 08:41:08 Re: Profiling postgresql queries