Use the Postgres window functions like rank(); this is what they're for.
http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-WINDOW
-- Darren Duncan
Alexander Farber wrote:
> Hello,
>
> I have a card game for each I'd like to introduce weekly tournaments.
> I'm going to save the score (virtual money) won by each player into:
>
> create table pref_money (
> id varchar(32) references pref_users,
> yw char(7) default to_char(current_timestamp, 'YYYY-WW'),
> money real
> );
> create index pref_money_yw_index on pref_money(yw);
>
> This way I don't have to perform any special calculations at the end
> of a week to find the weekly winner - just select all records for the
> current year-week, sort them by "money" column and take the 1st one.
>
> But I wonder, if there is a nice way in SQL to tell an interested user
> his current rank in the table? I.e. given a user "id", sort all records
> by the "money" column and then let him know his rank.
>
> I'm sure I can implement this in Perl, but then this will have to be
> a cronjob, because I'll have to perform somewhat complex
> calculations for each user "id" and store them into another table.
>
> But a cronjob isn't nice, because it won't show the rank in "real time".
>
> Is there maybe an elegant and quick way for this in SQL?
>
> Thank you for any ideas
> Alex
>
> P.S.: Using postgresql-8.4.5-1PGDG.rhel5 with CentOS 5.5