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 A00629DCA8D for ; Sun, 12 Mar 2006 15:59:24 -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 77038-06 for ; Sun, 12 Mar 2006 15:59:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from rockridge.uits.indiana.edu (rockridge.uits.indiana.edu [129.79.1.74]) by postgresql.org (Postfix) with ESMTP id 4C9BE9DCAA2 for ; Sun, 12 Mar 2006 15:59:22 -0400 (AST) Received: from mail-relay.iu.edu (logchain.uits.indiana.edu [129.79.1.77]) by rockridge.uits.indiana.edu (8.12.10/8.12.10/IUPO) with ESMTP id k2CJxJEU025195; Sun, 12 Mar 2006 14:59:20 -0500 (EST) Received: from [192.168.0.3] (149-159-1-54.dhcp-bl.indiana.edu [149.159.1.54]) (authenticated bits=0) by mail-relay.iu.edu (8.12.10/8.12.10/IUPO) with ESMTP id k2CJxK9M026624; Sun, 12 Mar 2006 14:59:20 -0500 (EST) Message-ID: <44147D97.5050301@indiana.edu> Date: Sun, 12 Mar 2006 14:59:19 -0500 From: Weimao Ke User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Daniel CAUNE CC: pgsql-sql@postgresql.org Subject: Re: pgsql aggregate: conditional max References: <0IW0005WSPTDIA40@VL-MH-MR002.ip.videotron.ca> In-Reply-To: <0IW0005WSPTDIA40@VL-MH-MR002.ip.videotron.ca> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.24 required=5 tests=[AWL=0.240] X-Spam-Score: 0.24 X-Spam-Level: X-Archive-Number: 200603/178 X-Sequence-Number: 24524 Daniel CAUNE wrote: > SELECT aid, cat > > FROM table, ( > SELECT aid, max(weight) as weight > FROM table > GROUP BY aid) AS tablemaxweight > WHERE table.aid = tablemaxweight.aid > AND table.weight = tablemaxweight.aid; > >There is a limit case you don't specify how to deal with, when two or more categories have the same maximum weight. The query I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid. > > Yes, this will introduce duplicates. Yet it is not too difficult to select only one for each aid from the results. Thank you! Weimao >-- >Daniel > > > > -- All best, Weimao Weimao Ke Indiana University Bloomington School of Library and Information Science http://ella.slis.indiana.edu/~wke