Nguyen,Diep T wrote on 12.05.2011 03:59:
Each ID can have different number of score counts.
My goal is to add column "order", which shows the order of the values
in column "date" in descendant order for each property. The expected output
will look like this:
id | date | score_count | order
----+------------------+---------------+-----------
13 | 1999-09-16 | 4 | 4
13 | 2002-06-27 | 4 | 3
13 | 2006-10-25 | 4 | 2
13 | 2010-05-12 | 4 | 1
65 | 2002-07-18 | 3 | 3
65 | 2004-08-05 | 3 | 2
65 | 2007-08-15 | 3 | 1
86 | 2001-05-29 | 5 | 5
86 | 2002-04-04 | 5 | 4
86 | 2006-03-02 | 5 | 3
86 | 2008-02-13 | 5 | 2
86 | 2011-01-19 | 5 | 1
Any help would be appreciated.
SELECT id,
date,
score_count,
row_number() over (partition by id order by date desc) as order_value
FROM your_table
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
