From middink@indo.net.id Sat May  1 13:33:24 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id D2419D1EAC1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  1 May 2004 10:33:24 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 87616-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  1 May 2004 10:33:06 -0300 (ADT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id 4B39FD1EB48
	for <pgsql-sql@postgresql.org>; Sat,  1 May 2004 10:32:59 -0300 (ADT)
Received: (qmail 7750 invoked from network); 1 May 2004 20:33:01 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 1 May 2004 20:33:01 +0700
Received: (qmail 5138 invoked from network); 1 May 2004 20:33:01 +0700
Received: from unknown (HELO smtp.surabaya.indo.net.id) (202.159.51.117)
	by smtp.indo.net.id with SMTP; 1 May 2004 20:33:01 +0700
Received: from smtp [202.159.51.117] by smtp.surabaya.indo.net.id
	(SMTPD32-7.10) id AA0511D008E; Sat, 01 May 2004 20:37:25 +0800
Received: FROM MIDDINKS BY smtp ; Sat May 01 20:37:23 2004 +0800
Message-ID: <002001c42f80$aa192330$4f00a8c0@MIDDINKS>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: <pgsql-sql@postgresql.org>
Subject: Rank
Date: Sat, 1 May 2004 21:30:53 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001B_01C42FC3.94983DF0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_40_50,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200405/27
X-Sequence-Number: 17741

This is a multi-part message in MIME format.

------=_NextPart_000_001B_01C42FC3.94983DF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear All,

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

What can I do to get result like that
------=_NextPart_000_001B_01C42FC3.94983DF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff background=3D""><FONT face=3DArial><FONT size=3D2>
<DIV>Dear All,</DIV>
<DIV>&nbsp;</DIV>
<DIV>I Have below table</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;id | site_name | point<BR>----+-----------+-------<BR>&nbsp; 1 |=
 Site=20
A&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40<BR>&nbsp; 2 | Site=20
B&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 90<BR>&nbsp; 3 | Site=20
D&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 22<BR>&nbsp; 4 | Site=20
X&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 98</DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV>&nbsp;Would like to calc that Rank for each site, and look like</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV></DIV>
<DIV>&nbsp;id | site_name | point |=20
rank<BR>----+-----------+-------+------<BR>&nbsp; 1 | Site A&nbsp;&nbsp;&nb=
sp;=20
|&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp;&nbsp;&nbsp;3<BR>&nbsp; 2 | Site=20
B&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 90 |&nbsp;&nbsp;&nbsp;&nbsp;2<BR>&n=
bsp;=20
3 | Site D&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 22=20
|&nbsp;&nbsp;&nbsp;&nbsp;4<BR>&nbsp; 4 | Site X&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp; 98 |&nbsp;&nbsp;&nbsp; 1</DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>What can I do to get result like=20
that</FONT></DIV></DIV></FONT></FONT></BODY></HTML>

------=_NextPart_000_001B_01C42FC3.94983DF0--





From middink@indo.net.id Sat May  1 18:24:46 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5E5B3D1EA09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  1 May 2004 15:24:46 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 57151-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  1 May 2004 15:24:27 -0300 (ADT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id 236ECD1EBAA
	for <pgsql-sql@postgresql.org>; Sat,  1 May 2004 15:24:23 -0300 (ADT)
Received: (qmail 32636 invoked from network); 2 May 2004 01:24:20 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 2 May 2004 01:24:20 +0700
Received: (qmail 24061 invoked from network); 2 May 2004 01:24:21 +0700
Received: from unknown (HELO smtp.surabaya.indo.net.id) (202.159.51.117)
	by smtp.indo.net.id with SMTP; 2 May 2004 01:24:21 +0700
Received: from smtp [202.159.51.117] by smtp.surabaya.indo.net.id
	(SMTPD32-7.10) id AE4FA770062; Sun, 02 May 2004 01:28:47 +0800
Received: FROM MIDDINKS BY smtp ; Sun May 02 01:28:40 2004 +0800
Message-ID: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: <pgsql-sql@postgresql.org>
Subject: Rank
Date: Sun, 2 May 2004 02:22:37 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_003B_01C42FEC.560F09F0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_40_50,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200405/30
X-Sequence-Number: 17744

This is a multi-part message in MIME format.

------=_NextPart_000_003B_01C42FEC.560F09F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

=20
Dear All,

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

What can I do to get result like that
------=_NextPart_000_003B_01C42FEC.560F09F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>&nbsp;
<DIV>Dear All,</DIV>
<DIV>&nbsp;</DIV>
<DIV>I Have below table</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;id | site_name | point<BR>----+-----------+-------<BR>&nbsp; 1 |=
 Site=20
A&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40<BR>&nbsp; 2 | Site=20
B&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 90<BR>&nbsp; 3 | Site=20
D&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 22<BR>&nbsp; 4 | Site=20
X&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 98</DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV>&nbsp;Would like to calc that Rank for each site, and look like</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV></DIV>
<DIV>&nbsp;id | site_name | point |=20
rank<BR>----+-----------+-------+------<BR>&nbsp; 1 | Site A&nbsp;&nbsp;&nb=
sp;=20
|&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp;&nbsp;&nbsp;3<BR>&nbsp; 2 | Site=20
B&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 90 |&nbsp;&nbsp;&nbsp;&nbsp;2<BR>&n=
bsp;=20
3 | Site D&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 22=20
|&nbsp;&nbsp;&nbsp;&nbsp;4<BR>&nbsp; 4 | Site X&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp; 98 |&nbsp;&nbsp;&nbsp; 1</DIV>
<DIV>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>What can I do to get result like=20
that</FONT></DIV></DIV></FONT></DIV></BODY></HTML>

------=_NextPart_000_003B_01C42FEC.560F09F0--





From martin@mk-os.de Tue May  4 13:42:43 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 771C3D1CAB0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 10:42:43 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 63704-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 10:42:25 -0300 (ADT)
Received: from p15124592.pureserver.info (p15124592.pureserver.info
	[217.160.181.164])
	by svr1.postgresql.org (Postfix) with ESMTP id BE8E1D1F025
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 10:42:20 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by p15124592.pureserver.info (Postfix) with ESMTP id CF71F1795A
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 15:42:22 +0200 (CEST)
Received: from webmail.mk-os.de (localhost [127.0.0.1])
	by p15124592.pureserver.info (Postfix) with SMTP id 3711917959
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 15:42:21 +0200 (CEST)
Received: from blueice4n1.de.ibm.com ([195.212.29.187])
	(SquirrelMail authenticated user martin)
	by webmail.mk-os.de with HTTP; Tue, 4 May 2004 15:42:21 +0200 (CEST)
Message-ID: <16616.195.212.29.187.1083678141.squirrel@webmail.mk-os.de>
In-Reply-To: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
Date: Tue, 4 May 2004 15:42:21 +0200 (CEST)
Subject: Re: Rank
From: "Martin Knipper" <martin@mk-os.de>
To: pgsql-sql@postgresql.org
Reply-To: martin@mk-os.de
User-Agent: SquirrelMail/1.4.1
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Priority: 3
Importance: Normal
X-Virus-Scanned: by AMaViS at MK-OS
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0
	tests=PRIORITY_NO_NAME
X-Spam-Level: 
X-Archive-Number: 200405/46
X-Sequence-Number: 17760

Hey,

Muhyiddin A.M Hayat sagte:
>
> Dear All,
>
> 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
>
> What can I do to get result like that

create temporary sequence ranking;
select *,nextval('ranking') as rank from yourTable order by site_name asc;

Greetins,
Martin Knipper

-- 
Martin Knipper
knipper@mk-os.de
http://www.mk-os.de



From gry@ll.mit.edu Tue May  4 14:12:04 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id BF386D1CA31
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 11:12:04 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70996-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 11:11:47 -0300 (ADT)
Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40])
	by svr1.postgresql.org (Postfix) with ESMTP id 4666FD1E2E1
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 11:11:42 -0300 (ADT)
Received: (from smtp@localhost)
	by ll.mit.edu (8.12.10/8.8.8) id i44EBXVw020848;
	Tue, 4 May 2004 10:11:33 -0400 (EDT)
Received: from UNKNOWN(            ), claiming to be "sty.llan"
	via SMTP by llmail, id smtpdAAA6ba4dO; Tue May  4 10:11:19 2004
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
X-Mailer: Sylpheed version 0.9.7 (GTK+ 1.2.10; i686-pc-linux-gnu)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200405/32
X-Sequence-Number: 17746

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)



From knipper@mk-os.de Tue May  4 15:13:06 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 132EED1F098
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 12:13:06 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 05950-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 12:12:44 -0300 (ADT)
Received: from p15124592.pureserver.info (p15124592.pureserver.info
	[217.160.181.164])
	by svr1.postgresql.org (Postfix) with ESMTP id 06DEFD1F078
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 12:12:44 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by p15124592.pureserver.info (Postfix) with ESMTP id F2BB81795A
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 17:12:42 +0200 (CEST)
Received: from sarge (p508A9FF5.dip0.t-ipconnect.de [80.138.159.245])
	by p15124592.pureserver.info (Postfix) with ESMTP id 103BB17959
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 17:12:42 +0200 (CEST)
Received: from mk-os.de (unknown [192.168.0.210])
	by sarge (Postfix) with ESMTP id E73633EC05
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 17:12:40 +0200 (CEST)
Message-ID: <4097B34B.7070102@mk-os.de>
Date: Tue, 04 May 2004 17:14:19 +0200
From: Martin Knipper <knipper@mk-os.de>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-AT;
	rv:1.6) Gecko/20040113
X-Accept-Language: de-at, de, en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Rank
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
	<20040504101119.58d355cb.gry@ll.mit.edu>
In-Reply-To: <20040504101119.58d355cb.gry@ll.mit.edu>
X-Enigmail-Version: 0.83.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS at MK-OS
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL,
	RCVD_IN_SORBS
X-Spam-Level: 
X-Archive-Number: 200405/38
X-Sequence-Number: 17752

Am 04.05.2004 16:11 schrieb george young:
> 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

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@mk-os.de

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



From pgadmin@itsbeen.sent.com Tue May  4 16:56:04 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 6E990D1E134
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 13:56:04 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 45523-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 13:55:43 -0300 (ADT)
Received: from out2.smtp.messagingengine.com (out2.smtp.messagingengine.com
	[66.111.4.26])
	by svr1.postgresql.org (Postfix) with ESMTP id A83CDD1F0A2
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 13:55:42 -0300 (ADT)
X-Sasl-enc: 2Pm+7+jAr8hzMq69L4SJ0g 1083689296
Received: from [10.10.12.12] (unknown [198.144.200.210])
	by www.fastmail.fm (Postfix) with ESMTP id B9374AF5053
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 12:48:16 -0400 (EDT)
Message-ID: <4097C924.2060307@itsbeen.sent.com>
Date: Tue, 04 May 2004 09:47:32 -0700
From: Rob <pgadmin@itsbeen.sent.com>
User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Rank
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
	<20040504101119.58d355cb.gry@ll.mit.edu>
	<4097B34B.7070102@mk-os.de>
In-Reply-To: <4097B34B.7070102@mk-os.de>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200405/42
X-Sequence-Number: 17756

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" <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
> 
> 
> 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?





From knipper@mk-os.de Tue May  4 17:37:55 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5E778D1F0C3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 14:37:55 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 72792-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 May 2004 14:37:33 -0300 (ADT)
Received: from p15124592.pureserver.info (p15124592.pureserver.info
	[217.160.181.164])
	by svr1.postgresql.org (Postfix) with ESMTP id 3ED6DD1E402
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 14:37:32 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by p15124592.pureserver.info (Postfix) with ESMTP id 6436C1795A
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 19:37:31 +0200 (CEST)
Received: from sarge (p508A9FF5.dip0.t-ipconnect.de [80.138.159.245])
	by p15124592.pureserver.info (Postfix) with ESMTP id B17F717959
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 19:37:30 +0200 (CEST)
Received: from mk-os.de (unknown [192.168.0.210])
	by sarge (Postfix) with ESMTP id 5D2553EC05
	for <pgsql-sql@postgresql.org>; Tue,  4 May 2004 19:37:30 +0200 (CEST)
Message-ID: <4097D53E.6060400@mk-os.de>
Date: Tue, 04 May 2004 19:39:10 +0200
From: Martin Knipper <knipper@mk-os.de>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-AT;
	rv:1.6) Gecko/20040113
X-Accept-Language: de-at, de, en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Rank
References: <003e01c42fa9$5677b240$4f00a8c0@MIDDINKS>
	<20040504101119.58d355cb.gry@ll.mit.edu>
	<4097B34B.7070102@mk-os.de> <4097C924.2060307@itsbeen.sent.com>
In-Reply-To: <4097C924.2060307@itsbeen.sent.com>
X-Enigmail-Version: 0.83.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS at MK-OS
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL,
	RCVD_IN_SORBS
X-Spam-Level: 
X-Archive-Number: 200405/43
X-Sequence-Number: 17757

Am 04.05.2004 18:47 schrieb Rob:

> 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" <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
>>>
>>>

[...]

>>
>>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;
>>
> 
> wouldn't it have to be:
> 
> select *, nextval('ranking') as rank
>    from yourTable
>   order by point desc;
> 
> for the ranking to work?
> 

Yes, you are right.
I guess I was a little confused with the ordering in the example
table Muhyiddin provided.

Greetings,
Martin

-- 
Martin Knipper
www  : http://www.mk-os.de
Mail : knipper@mk-os.de