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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
