Re: Finding rank of a single record

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'