@ Igor - Thank you thats a fantastic example. :D @ Gerry - Thanks. Your right. Diplicates are ranked one point below. Your right, Instead of joint points being ranked as 3rd place, they are being ranked at the lowest rank. This is what I will end up using. update stats set rank = (select count(*) + 1 from stats b where b.points > stats.points); Many thanks the both of you for your help with this. I realy needed it.
ö¿ô \_/ Gerry Snyder-4 wrote: > > Igor Tandetnik wrote: >> "Pejayuk" <[EMAIL PROTECTED]> wrote in >> message news:[EMAIL PROTECTED] >> >>> I have a player stats table with a points field. >>> I want to update the rank in this using a single query based on the >>> points in the row. >>> I am not that good at complex sql but I have managed to get the >>> result I want from the following select query. >>> >>> SELECT points,(SELECT COUNT(*) FROM stats b WHERE b.points >= >>> a.points ORDER BY points ASC) AS rank FROM stats a >>> >>> This gives me a list of all points and their rank correctly. >>> What I actualy need is an update query along the same lines >>> >> >> update stats set rank = >> (select count(*) from stats b where b.points >= stats.points); >> >> Igor Tandetnik >> > That does not quite seem to work when there are ties. How about: > > update stats set rank = > (select count(*)+1 from stats b where b.points > stats.points); > > > Gerry > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Updating-rank-field-by-points-in-stats-table.-tp18146541p18150344.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
