Postgres Pro
Downloads
Home   >   mailing lists

Thread: Finding rank of a single record

Finding rank of a single record

From
Alexander Farber
Date:
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)

Re: Finding rank of a single record

From
Rob Sargent
Date:
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'

Re: Finding rank of a single record

From
Alexander Farber
Date:
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)

Re: Finding rank of a single record

From
Alexander Farber
Date:
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

Re: Finding rank of a single record

From
Rob Sargent
Date:

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.