Обсуждение: Rank

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)

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++;

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?

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

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