Forum OpenACS Q&A: SQL question

I have a table with the columns player_id and rating and would like to query PostgreSQL in a way so that it returns "ranking" as well, like so:

player_id	rating	ranking
1	210	1
2	112	2
3	112	2
4	25	3

(Players with identical rating should have the same ranking)

Anyone know how this can be achieved?

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.