Re: Rank

Am 04.05.2004 16:11 schrieb george young:
> On Sun, 2 May 2004 02:22:37 +0800
> "Muhyiddin A.M Hayat" <middink(at)indo(dot)net(dot)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

--
Martin Knipper
www : http://www.mk-os.de
Mail : knipper(at)mk-os(dot)de

Random Signature:
-----------------
while( !asleep() ) sheep++;