Re: SQL prodecure to compute ranking

Jonathan Lewis wrote:
> There is a potential problem with a solution of
> this type, that depends on the requirements
> for tied places. The rownum is not the rank
> if you have tied scores.

Yap, but i wanted to give just a minimal solution and i just simplified what he is using now without the use of analytical functions just using plain sql; anyway you can always add something like a timestamp for the user first registration to avoid ties.
I think it's possible emulate ranking of oracle with pl/sql is this way

create package my_package
last_ranking number := 0;
last_points number :=0;

end;
/

create or replace function ranking(points_of_user in number) return number as
begin

   if my_package.last_points!=points_of_user then      my_package.last_ranking := my_package.last_ranking+1;    end if;
   my_package.last_points:=points_of_user; return my_package.last_ranking;
end;
/

create or replace procedure reset_ranking as begin

   my_package.last_points:=-1;
   my_package.last_ranking:=0;
end;
/

before query

execute reset_ranking ;
select ranking(points),a.* from (select * from users order by points) a;

this works but i think there would be an overhead due the context switch (sql/pls-sql) for every row Received on Fri Sep 13 2002 - 07:51:02 CDT