(Players with identical rating should have the same ranking)
Anyone know how this can be achieved?
I shudder to think how the query plan would look for this.SELECT p.player_id, p.rating, q.ranking FROM player_rating_tab p, (SELECT r.rating rating, rownum ranking FROM (SELECT DISTINCT rating FROM acs_tab ORDER BY rating DESC) r ) q WHERE p.rating = q.rating ORDER BY q.ranking SQL> / PLAYER_ID RATING RANKING ---------- ---------- ---------- 1 210 1 2 112 2 3 112 2 4 25 3 SQL>
You'll find plenty of better answers, I'm sure. Joe Celko must have answered this type of question hundreds of times so if you have access to his columns or books, that might be a good place to look.
If I had to do this for real, I'd use Jonathan's advice and do it in a procedure.
"rownum" doesn't exist in PG 7.1.3, it seems, at least the query choked on that.
I am using "ad_table" (this is one GREAT proc) in 3.2.5 to display the results to make the columns sortable. Modifying ad_table should be easy and so I'll follow your recomendations. Thanks again.