From: Rob <pgadmin@itsbeen.sent.com>
To: pgsql-sql@postgresql.org
Subject: Re: Rank
Date: Tue, 04 May 2004 09:47:32 -0700
Message-ID: <4097C924.2060307@itsbeen.sent.com> (raw)
In-Reply-To: <4097B34B.7070102@mk-os.de>
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
<20040504101119.58d355cb.gry@ll.mit.edu>
<4097B34B.7070102@mk-os.de>
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" <middink@indo.net.id> 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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgadmin@itsbeen.sent.com
Subject: Re: Rank
In-Reply-To: <4097C924.2060307@itsbeen.sent.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox