Re: SQL prodecure to compute ranking

From: CANDIDO DESSANTI <termy_at_blunet.it>
Date: Sat, 14 Sep 2002 09:23:19 +0200
Message-ID: <3D82E3E7.9060600@blunet.it>

Jonathan Lewis wrote:
> Definitely worth doing, since the analytic features
> are not available to standard edition.
>
> Your pl/sql produces the method that the analytic
> functions label as 'dense_rank()' by the way.
>
> If two people tie for third place, the next person
> is given 5th place if you want to emulate the rank()
> function.

ok you won the real rank function then :)

create or replace package my_package as
last_ranking number := 0;
last_points number :=-1;
ties 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+my_package.ties;
     my_package.ties := 0;
   else
     my_package.ties := my_package.ties+1;

   end if;
   my_package.last_points:=points_of_user; return my_package.last_ranking;
end;
/

create or replace function dense_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_rankings as begin

   my_package.last_points:=-1;
   my_package.last_ranking:=0;
   my_package.ties:=0;

end;
/ Received on Sat Sep 14 2002 - 02:23:19 CDT