Re: Finding rank of a single record

Alexander Farber wrote:
> And:
>
> pref=> 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')
> ) ar
> where ar.id='OK138239987797';
>
>
> works, but delivers 800 different ranks:
>
>        id       | first_name |    city     | money |  rank
> ----------------+------------+-------------+-------+---------
>  OK138239987797 | Иван       | Новосибирск |  2169 |       1
>  OK138239987797 | Иван       | Новосибирск |  1955 |    3479
>  OK138239987797 | Иван       | Новосибирск |  1948 |    6957
>  OK138239987797 | Иван       | Новосибирск |  1060 |   10435
>  OK138239987797 | Иван       | Новосибирск |  1034 |   13913
>  OK138239987797 | Иван       | Новосибирск |  1012 |   17391
>  OK138239987797 | Иван       | Новосибирск |   929 |   20869
>
>
I haven't created your tables, but it looks to me like the sub-select
needs something from u to m.  When run by itself what does the
sub-select generate.  (I'm tempted to recomment count(*) cause I think
you're getting a cross-product.)  This could all be a cut / paste error
in my first response.