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
