From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Finding rank of a single record |
Date: | 2010-11-07 01:02:51 |
Message-ID: | 4CD5FABB.1030608@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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'
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Brown-Bayliss | 2010-11-07 01:13:47 | Re: Libpq is very slow on windows but fast on linux. |
Previous Message | Craig Ringer | 2010-11-07 00:23:56 | Re: PITR on different machine/architecture? |