Finding rank of a single record

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Finding rank of a single record
Date: 2010-11-06 23:18:55
Message-ID: AANLkTi=MsRjjY4RS41xbpSULm=vs5JeE-hb8mve29w+g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

pref=> \d pref_users;
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(32) |
last_name | character varying(32) |
female | boolean |
avatar | character varying(128) |
city | character varying(32) |
lat | real |
lng | real |
last_login | timestamp without time zone | default now()
last_ip | inet |
medals | smallint |
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pref_users_lat_check" CHECK ((-90)::double precision <= lat AND
lat <= 90::double precision)
"pref_users_lng_check" CHECK ((-90)::double precision <= lng AND
lng <= 90::double precision)
"pref_users_medals_check" CHECK (medals > 0)
Referenced by:
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_rate" CONSTRAINT "pref_rate_obj_fkey" FOREIGN KEY
(obj) REFERENCES pref_users(id)
TABLE "pref_rate" CONSTRAINT "pref_rate_subj_fkey" FOREIGN KEY
(subj) REFERENCES pref_users(id)

pref=> \d pref_money;
Table "public.pref_money"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------------
id | character varying(32) |
money | integer | not null
yw | character(7) | default to_char(now(), 'YYYY-IW'::text)
Indexes:
"pref_money_yw_index" btree (yw)
Foreign-key constraints:
"pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Brown-Bayliss 2010-11-06 23:54:04 Libpq is very slow on windows but fast on linux.
Previous Message Jasen Betts 2010-11-06 21:29:38 Re: avoiding nested loops when joining on partitioned tables