ordering by date for each ID

From: "Nguyen,Diep T" <diep(at)ufl(dot)edu>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: ordering by date for each ID
Date: 2011-05-12 01:59:08
Message-ID: AC6F23A2BA13C347A59BDCBCFF41E27B6954E47BAC@UFEXCH-MBXCL03.ad.ufl.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi all,
I have this table
id | date | score_count
----+--------------+-------------
13 | 1999-09-16 | 4
13 | 2002-06-27 | 4
13 | 2006-10-25 | 4
13 | 2010-05-12 | 4
65 | 2002-07-18 | 3
65 | 2004-08-05 | 3
65 | 2007-08-15 | 3
86 | 2001-05-29 | 5
86 | 2002-04-04 | 5
86 | 2006-03-02 | 5
86 | 2008-02-13 | 5
86 | 2011-01-19 | 5

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.

Thanks,

Diep

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2011-05-12 11:01:41 Re: CROSS JOIN ordering
Previous Message Grzegorz Szpetkowski 2011-05-11 23:19:33 CROSS JOIN ordering