Try this:
CREATE TEMPORARY TABLE tmpRankings (
Rank int auto_increment,
entries int,
user_id int
)
INSERT tmpRankings (points, user_id)
SELECT count(1), user_id
FROM sometablenamehere
GROUP BY user_id
ORDER BY entries DESC;
This way the tmpRankings table contains an ordered, numbered list of all
of your user_id's (in proper ranking order). If you change tmpRankings
from a Temporary table to a permanent table you could rebuild it each time
someone makes a new entry. That would save you the overhead of recomputing
the entire table each time someone just wants to know their ranking but
the data hasn't changed because nobody has made any entries since the last
check.
There are other tweaks you can do to save recomputes, too, but this is
the general idea.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Michael J. Pawlowsky" <[EMAIL PROTECTED]> wrote on 12/22/2004 04:00:45
PM:
> Iâm trying to come up with a more efficient method to do this.
> I have a table where people enter some info into the table.
>
> The more entries they add the more âpointsâ they get.
> (1 point per entry).
>
> I would like to allow the users to be able to see where they stand rank
> wise with everyone else.
> Right now I basically do a SELECT count(1) as entries, user_id GROUP BY
> user_id ORDER BY entries DESC.
>
> Then loop through the results until I match their user_id and count how
> many times I go through the loop and that is how I can give them their
> ranking. It just seems like a big waste to loop through the results
> until I find their user_id.
>
> I was wondering if anyone could think of a better way to do this.
>
> Thanks,
> Mike
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
