Trying to rank the rows in my table and then update a field in each row with its rank number. The ranking will be based on a field with a points value.
The database structure is as follows:
Table name: blogs
fields: ID, buildpointsNow, build_rank
I want to run a cron job that will rank each row depending on its buildpointsNow value and then update its build_rank with that rank number.
So far i have tried:
UPDATE blogs
JOIN (SELECT p.id,
@curRank := @curRank + 1 AS rank
FROM blogs p
JOIN (SELECT @curRank := 0) r
ORDER BY p.buildpointsNow DESC
) ranks ON (ranks.id = blogs.id)
SET blogs.build_rank = blogs.build_rank;
And also:
update blogs cross join
(select @rn := 0) vars
set build_rank = (@rn := @rn + 1);
order by buildpointsNow;
Both of these throw no errors and do update the database rows. However they do not order the builds rank depending on the buildpointsNow field. They instead update each row in its order of creation / its ID.
Any ideas?
blogs need to be ordered by buildpointsNow before adding @curRank:
update blogs
join (
select p.id, @curRank := @curRank + 1 as rank
from (select id from blogs order by buildpointsNow) p
join (select @curRank := 0) r) ranks on ranks.id = blogs.id
set blogs.build_rank = ranks.rank;