SQL prodecure to compute ranking

From: gary gibbons <aalegrias_at_yahoo.com>
Date: 10 Sep 2002 08:58:05 -0700
Message-ID: <900c6d6a.0209100758.1c44ea27@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 - 10:58:05 CDT