X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9529F9DC861 for ; Wed, 28 Dec 2005 21:31:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85924-09 for ; Wed, 28 Dec 2005 21:31:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id 6FF159DC80A for ; Wed, 28 Dec 2005 21:31:35 -0400 (AST) Received: (qmail 23663 invoked from network); 29 Dec 2005 02:31:51 +0100 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 29 Dec 2005 02:31:51 +0100 To: "Collin Peters" , pgsql-sql@postgresql.org Subject: Re: Help with simple query References: Message-ID: From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Date: Thu, 29 Dec 2005 02:31:50 +0100 In-Reply-To: User-Agent: Opera M2/8.51 (Linux, build 1462) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/233 X-Sequence-Number: 23775 If you want the latest by user, you can cheat a bit and use the fact that the id's are incrementing, thus ordering by the id is about the same as ordering by the date field. I know it can be inexact in some corner cases, but it's a good approximation, and very useful in practice : SELECT user_id, max(note_id) FROM notes GROUP by user_id; So : SELECT * FROM notes WHERE id IN (SELECT max(note_id) FROM notes GROUP by user_id) ; Can postgres use the index on these max() clauses now ?