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
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 |