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 8570A5E46C0 for ; Fri, 20 Aug 2004 14:09:29 -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 03274-01 for ; Fri, 20 Aug 2004 17:09:24 +0000 (GMT) Received: from 10.hostcorporate.com (unknown [69.41.231.186]) by svr1.postgresql.org (Postfix) with SMTP id 854725E46ED for ; Fri, 20 Aug 2004 14:09:22 -0300 (ADT) Received: (qmail 1601 invoked from network); 20 Aug 2004 17:00:38 -0000 Received: from localhost (HELO localhost.localdomain) (127.0.0.1) by localhost.10.hostcorporate.com with SMTP; 20 Aug 2004 17:00:38 -0000 Message-ID: <412630C8.5000004@zara.6.isreserved.com> Date: Sat, 21 Aug 2004 00:11:36 +0700 From: David Garamond User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7b) Gecko/20040421 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: olympics ranking query References: <41262968.2000507@zara.6.isreserved.com> <22979.1093020382@sss.pgh.pa.us> In-Reply-To: <22979.1093020382@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/230 X-Sequence-Number: 18727 Tom Lane wrote: >>Challenge question: is there a simpler way to do query #1 (without any >>PL, and if possible without sequences too? > > Can't without sequences AFAIK, but you certainly can do it simpler: > > select setval('seq1', 0); > > select nextval('seq1'), * from > (select count(*) as numranker, > gold, silver, bronze > from countrymedal > group by gold, silver, bronze > order by gold desc, silver desc, bronze desc) ss; This is not quite the same. The ranks are sequential, but they skip, so as to match the number of participating countries. -- dave