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" <[EMAIL PROTECTED]> 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?
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
