Search Postgresql Archives

Sorting rows by a column and storing a row number

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux