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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
