X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2781A5E46C4 for ; Wed, 25 Aug 2004 15:21:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88436-02 for ; Wed, 25 Aug 2004 18:21:07 +0000 (GMT) Received: from smtp.unc.edu (smtpsrv11.isis.unc.edu [152.2.1.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2CB1A5E46EE for ; Wed, 25 Aug 2004 15:21:03 -0300 (ADT) Received: from perrin.socsci.unc.edu (mail@perrin.socsci.unc.edu [152.2.242.197]) by smtp.unc.edu (8.12.9/8.12.9) with ESMTP id i7PIKpCG011746; Wed, 25 Aug 2004 14:20:54 -0400 (EDT) Received: from clists (helo=localhost) by perrin.socsci.unc.edu with local-esmtp (Exim 3.36 #1 (Debian)) id 1C02OC-0005SD-00; Wed, 25 Aug 2004 14:20:52 -0400 Date: Wed, 25 Aug 2004 14:20:52 -0400 (EDT) From: Andrew Perrin To: Josh Berkus Cc: pgsql-sql@postgresql.org Subject: Re: Complicated "group by" question In-Reply-To: <200408251047.18645.josh@agliodbs.com> Message-ID: References: <200408251047.18645.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/267 X-Sequence-Number: 18764 Excellent - thanks, Josh! ---------------------------------------------------------------------- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu On Wed, 25 Aug 2004, Josh Berkus wrote: > Andrew, > > > I have a table of people ("reviewers"), a table of review assignments > > ("assign"), and a table of review acceptances ("accept"). I would like to > > be able to write a query to return the latest (e.g., max(assign_date)) > > assignment for each reviewer, plus the acc_id field from "accept". I > > think I should be able to do this with a GROUP BY clause, but am having no > > luck. > > Some vagueness: you didn't say whether you wanted to see two assignments if > they have the same, latest date. Nor did you specify whether you wanted to > see assignments that had not been accepted (the below assumes yes to both) > > Hmmm ... one way, SQL-standard: > > SELECT reviewer.name, assign_date, acc_id > FROM reviewers JOIN assign ON reviewer.id = assign.reviewer_id > LEFT OUTER JOIN accept ON assign.id = accept.assign_id > WHERE assign_date IN (SELECT max(ass2.assign_date) FROM assign ass2 > WHERE ass2.reviewer_id = reviewers.id) > > or for a bit faster execution on PG you cann replace that WHERE clause with: > > WHERE assign_date IN (SELECT ass2.assign_date FROM assign ass2 > WHERE ass2.reviewer_id = reviewers.id ORDER BY ass2.assign_date DESC LIMIT 1) > > -- > Josh Berkus > Aglio Database Solutions > San Francisco >