Date: Tue, 4 May 2004 10:11:19 -0400
From: george young <gry@ll.mit.edu>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rank
Message-Id: <20040504101119.58d355cb.gry@ll.mit.edu>
In-Reply-To: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)