Alexander Farber wrote:
Hello,
I have 2 tables with user infos (please see \d output at the bottom)
and would like to find their rank depending on their "money".
When I select all records, the rank() works fine:
pref=> select u.first_name,
u.city,
m.money,
rank() over (order by money desc)
from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, 'YYYY-IW') and
u.id=m.id;
first_name | city | money | rank
------------------------------+---------------------------------+-------+------
ÐÐÐÐÑÐÐÐÑ | ÐÑÐÑÐÐÑ | 2169 | 1
jorj | | 1955 | 2
ÐÐÑÐÐÐ | 158 | 1948 | 3
ÐÐÐÐÑÐÐ | 1505941 | 1060 | 4
ÐÐÑÐÑ | ÐÐÐÐÐ | 1034 | 5
ÑÐÑÐÐÐ | | 1012 | 6
.....................
But when I try to select a single record, then I always get the rank 1:
pref=> select u.id,
u.first_name,
u.city,
m.money,
rank() over (order by money desc)
from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, 'YYYY-IW') and
u.id=m.id and u.id='OK138239987797';
id | first_name | city | money | rank
----------------+------------+-------------+-------+------
OK138239987797 | ÐÐÐÐ | ÐÐÐÐÑÐÐÐÑÑÐ | 468 | 1
(1 row)
(I guess because my "window" is 1 row only)
Please give me a hint how to select just 1 record
and still find it's correct rank compared to other.
Or do I have to introduce a 3rd table holding ranks
and update it by a cronjob?
Regards
Alex
P.S. the 2 tables are:
ince the rank is only appropriate over a given set I think you'll have
to take a sub-select approach:
select * from (
select u.id,
u.first_name,
u.city,
m.money,
rank() over (order by money desc)
from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, 'YYYY-IW')) all_ranks ar
where ar.id='OK138239987797'
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
