Finding rank of a single record

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'

Sorry Rob, but it fails with:

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')
) all_ranks ar
where ar.id='OK138239987797';
ERROR: syntax error at or near "ar"
LINE 9: ) all_ranks ar
^

Do you have a doc pointer for me here?

I've forgotten to mention that I'm using PostgreSQL 8.4.5

Regards
Alex

P.S.: Here again my tables, I'm trying to get a rank for 1 user there:

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)

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

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.