views:

62

answers:

0

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.

I'm using hibernate, so cannot execute any queries with variables, so I came up with this query which is very inefficient. Can this be optimized to work with the constraints specified above?

update player g1 
    set g1.rank = 1 + 
    ((SELECT count(*) from 
    (select * from player) g2 
    where g2.points > g1.points))