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'
