From: george young <gry@ll.mit.edu>
To: Muhyiddin A.M Hayat <middink@indo.net.id>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rank
Date: Tue, 4 May 2004 10:11:19 -0400
Message-ID: <20040504101119.58d355cb.gry@ll.mit.edu> (raw)
In-Reply-To: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
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)
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: gry@ll.mit.edu, middink@indo.net.id
Subject: Re: Rank
In-Reply-To: <20040504101119.58d355cb.gry@ll.mit.edu>
* 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