Re: ordering by date for each ID - Mailing list pgsql-sql

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