To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: ordering by date for each ID
Date: Thu, 12 May 2011 22:45:21 +0200
Lines: 30
Message-ID: <iqhgso$u61$1@dough.gmane.org>
References: 
 <AC6F23A2BA13C347A59BDCBCFF41E27B6954E47BAC@UFEXCH-MBXCL03.ad.ufl.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: 
 <AC6F23A2BA13C347A59BDCBCFF41E27B6954E47BAC@UFEXCH-MBXCL03.ad.ufl.edu>

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