Re: ordering by date for each ID

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