pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRank
7+ messages / 5 participants
[nested] [flat]
* Rank
@ 2004-05-01 13:30 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 0 replies; 7+ messages in thread
From: Muhyiddin A.M Hayat @ 2004-05-01 13:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Rank
@ 2004-05-01 18:22 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 2 replies; 7+ messages in thread
From: Muhyiddin A.M Hayat @ 2004-05-01 18:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rank
@ 2004-05-04 13:42 Martin Knipper <martin@mk-os.de>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
1 sibling, 0 replies; 7+ messages in thread
From: Martin Knipper @ 2004-05-04 13:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rank
@ 2004-05-04 14:11 george young <gry@ll.mit.edu>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
1 sibling, 1 reply; 7+ messages in thread
From: george young @ 2004-05-04 14:11 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: 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 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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rank
@ 2004-05-04 15:14 Martin Knipper <knipper@mk-os.de>
parent: george young <gry@ll.mit.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Knipper @ 2004-05-04 15:14 UTC (permalink / raw)
To: pgsql-sql
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++;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rank
@ 2004-05-04 16:47 Rob <pgadmin@itsbeen.sent.com>
parent: Martin Knipper <knipper@mk-os.de>
0 siblings, 1 reply; 7+ messages in thread
From: Rob @ 2004-05-04 16:47 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Rank
@ 2004-05-04 17:39 Martin Knipper <knipper@mk-os.de>
parent: Rob <pgadmin@itsbeen.sent.com>
0 siblings, 0 replies; 7+ messages in thread
From: Martin Knipper @ 2004-05-04 17:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-05-04 17:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-01 13:30 Rank Muhyiddin A.M Hayat <middink@indo.net.id>
2004-05-01 18:22 Rank Muhyiddin A.M Hayat <middink@indo.net.id>
2004-05-04 13:42 ` Martin Knipper <martin@mk-os.de>
2004-05-04 14:11 ` george young <gry@ll.mit.edu>
2004-05-04 15:14 ` Martin Knipper <knipper@mk-os.de>
2004-05-04 16:47 ` Rob <pgadmin@itsbeen.sent.com>
2004-05-04 17:39 ` Martin Knipper <knipper@mk-os.de>
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