Re: SQL prodecure to compute ranking

You might want to look that Oracle's SQL Analytic Functions They allow you to do things like
SELECT sales_person, sales_region, sales_amount,

      RANK() OVER (PARTITION BY s_region ORDER BY s_amount DESC ) FROM sales_table;

"gary gibbons" <aalegrias_at_yahoo.com> wrote in message news:900c6d6a.0209100758.1c44ea27_at_posting.google.com...
> Oracle 8i
>
> I am working on a high use web site and have some questions on fine tuning
> my high score board. Basically, the requirements state that the high score
> board should display the top 5 scores, the users score (with rank) along
> with the 2 above and 2 below. Of course if the user is in the top 10, it
> just returns the top 10.
>
> The table size is 800,000 and growing . Oracle 8i running on a
 Sun/Solaris
>
>
>
> The table definition is under design as well.
>
> I've figured out a way to do this within a SP using a UNION, but am
> wondering if anyone has any better ideas. In particular I am wondering if
> there is a way to write it to insure that the sort on points happens only
> once. Using cursors and building a delimited text string is one idea, does
> any one have an insight on this that can help us?
>
> excerpts from SP follow (error handling omitted):
>
> SELECT points INTO User_Points FROM member WHERE memberid = Userid_IN;
> SELECT count(*) INTO User_Rank FROM member WHERE points > User_Points;
>
> SELECT ROWNUM as rank, memberid, points, username
> FROM (SELECT memberid, points, username
> FROM member
> ORDER BY points DESC)
> WHERE ROWNUM <= 5
> UNION
> SELECT User_Rank as rank, memberid, points, username
> FROM member WHERE memberid=Userid_IN
> UNION
> SELECT User_Rank-ROWNUM AS rank, memberid, points, username
> FROM (SELECT memberid, points, username
> FROM member
> WHERE points >= User_Points AND memberid != Userid_IN
> ORDER BY points)
> WHERE ROWNUM <= 2
> UNION
> SELECT User_Rank+ROWNUM AS rank, memberid, points, username
> FROM (SELECT memberid, points, username
> FROM member
> WHERE points < User_Points AND memberid != Userid_IN
> ORDER BY points DESC)
> WHERE ROWNUM <= 2
> ORDER BY points DESC;
>
>
>
>
>
>
> thanks
>
> gary gibbons
Received on Tue Sep 10 2002 - 13:10:40 CDT