Forum OpenACS Q&A: Response to SQL question

I don't have a Postgresql query but the following works in Oracle (and I know it's irrelevant but I've done it now, so there).

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>

I shudder to think how the query plan would look for this.

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.