Re: Finding rank of a single record - Mailing list pgsql-general

From Alexander Farber
Subject Re: Finding rank of a single record
Date
Msg-id AANLkTimKHK6sYEHpaZPQz6rEUH-ky=k-d2cPydiMAieE@mail.gmail.com
Whole thread Raw
In response to Re: Finding rank of a single record  (Rob Sargent <robjsargent@gmail.com>)
Responses Re: Finding rank of a single record
List pgsql-general
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)

pgsql-general by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: function with multiple return values
Next
From: Alexander Farber
Date:
Subject: Re: Finding rank of a single record