Thread: Sorting rows by a column and storing a row number

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

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