X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6E990D1E134 for ; Tue, 4 May 2004 13:56:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 45523-10 for ; Tue, 4 May 2004 13:55:43 -0300 (ADT) Received: from out2.smtp.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by svr1.postgresql.org (Postfix) with ESMTP id A83CDD1F0A2 for ; Tue, 4 May 2004 13:55:42 -0300 (ADT) X-Sasl-enc: 2Pm+7+jAr8hzMq69L4SJ0g 1083689296 Received: from [10.10.12.12] (unknown [198.144.200.210]) by www.fastmail.fm (Postfix) with ESMTP id B9374AF5053 for ; Tue, 4 May 2004 12:48:16 -0400 (EDT) Message-ID: <4097C924.2060307@itsbeen.sent.com> Date: Tue, 04 May 2004 09:47:32 -0700 From: Rob User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Rank References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS> <20040504101119.58d355cb.gry@ll.mit.edu> <4097B34B.7070102@mk-os.de> In-Reply-To: <4097B34B.7070102@mk-os.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/42 X-Sequence-Number: 17756 Martin Knipper wrote: > Am 04.05.2004 16:11 schrieb george young: > >>On Sun, 2 May 2004 02:22:37 +0800 >>"Muhyiddin A.M Hayat" threw this fish to the penguins: >> >> >>>I Have below table >>> >>>id | site_name | point >>>----+-----------+------- >>> 1 | Site A | 40 >>> 2 | Site B | 90 >>> 3 | Site D | 22 >>> 4 | Site X | 98 >>> >>>Would like to calc that Rank for each site, and look like >>> >>>id | site_name | point | rank >>>----+-----------+-------+------ >>> 1 | Site A | 40 | 3 >>> 2 | Site B | 90 | 2 >>> 3 | Site D | 22 | 4 >>> 4 | Site X | 98 | 1 >> >> >>Well, a simple minded solution would be: >> >>select id,site_name,point,(select count(*)from mytable t2 >> where t2.point >= t1.point) as rank from mytable t1; >> >> id | site_name | point | rank >>----+-----------+-------+------ >> 4 | Site X | 98 | 1 >> 2 | Site B | 90 | 2 >> 1 | Site A | 40 | 3 >> 3 | Site D | 22 | 4 >>(4 rows) >> >>If mytable is huge this may be prohibitively slow, but it's worth a try. >>There's probably a self join that would be faster. Hmm... in fact: >> >>select t1.id,t1.site_name,t1.point,count(t2.point) as rank from mytable t1,mytable t2 >> where t2.point >=t1.point group by t1.id,t1.site_name,t1.point; >> >> id | site_name | point | rank >>----+-----------+-------+------ >> 3 | Site D | 22 | 4 >> 2 | Site B | 90 | 2 >> 4 | Site X | 98 | 1 >> 1 | Site A | 40 | 3 >>(4 rows) >> >> >>-- George Young > > > Another possibilty is to use a sequence: > > demo=# create temporary sequence ranking; > demo=# select *,nextval('ranking') as rank from yourTable order by > site_name asc; > > Greetins, > Martin > wouldn't it have to be: select *, nextval('ranking') as rank from yourTable order by point desc; for the ranking to work?