I had asked the following question
I have the following table structure for a table Player
Table Player {
Long playerID;
Long points;
Long rank;
}
Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank.
And received the answer from Daniel Vassalo (thank you).
UPDATE player
JOIN (SELECT p.playerID,
IF(@lastPoint <> p.points,
@curRank := @curRank + 1,
@curRank) AS rank,
IF(@lastPoint = p.points,
@curRank := @curRank + 1,
@curRank),
@lastPoint := p.points
FROM player p
JOIN (SELECT @curRank := 0, @lastPoint := 0) r
ORDER BY p.points DESC
) ranks ON (ranks.playerID = player.playerID)
SET player.rank = ranks.rank;
When I try to execute this as a native query in Hibernate, the following exception is thrown.
java.lang.IllegalArgumentException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':'
Apparently this has been an open issue for the last couple of years, I want to know if the ranking query can be made to work either
Without using any variables in the SQL query OR
Using any workaround for Hibernate.
