Re: Rank - Mailing list pgsql-sql

From george young
Subject Re: Rank
Date
Msg-id 20040504101119.58d355cb.gry@ll.mit.edu
Whole thread Raw
In response to Rank  ("Muhyiddin A.M Hayat" <middink@indo.net.id>)
Responses Re: Rank
List pgsql-sql
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
byt1.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
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)


pgsql-sql by date:

Previous
From: "ashok@kalculate.com"
Date:
Subject: typecasting numeric(18,4) to varchar/text
Next
From: "Andrei Bintintan"
Date:
Subject: Re: start