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)
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 |