From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: ordering by date for each ID
Date: Thu, 12 May 2011 22:45:21 +0200
Message-ID: <iqhgso$u61$1@dough.gmane.org> (raw)
In-Reply-To: <AC6F23A2BA13C347A59BDCBCFF41E27B6954E47BAC@UFEXCH-MBXCL03.ad.ufl.edu>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: ordering by date for each ID
In-Reply-To: <iqhgso$u61$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox