Search Postgresql Archives

Re: Finding rank of a single record

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux