Postgres Pro
Downloads
Home   >   mailing lists

Thread: ordering by date for each ID

ordering by date for each ID

From
"Nguyen,Diep T"
Date:
Hi all,
I have this tableid |           date | score_count
----+--------------+-------------13 | 1999-09-16   |           413 | 2002-06-27   |           413 | 2006-10-25   |
    413 | 2010-05-12   |           465 | 2002-07-18   |           365 | 2004-08-05   |           365 | 2007-08-15   |
       386 | 2001-05-29   |           586 | 2002-04-04   |           586 | 2006-03-02   |           586 | 2008-02-13
|          586 | 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        | 413 | 2002-06-27   |
  4        | 313 | 2006-10-25   |           4        | 213 | 2010-05-12   |           4        | 165 | 2002-07-18   |
       3        | 365 | 2004-08-05   |           3        | 265 | 2007-08-15   |           3        | 186 | 2001-05-29
|           5        | 586 | 2002-04-04   |           5        | 486 | 2006-03-02   |           5        | 386 |
2008-02-13  |           5        | 2 86 | 2011-01-19   |           5        | 1 

Any help would be appreciated.

Thanks,

Diep

Re: ordering by date for each ID

From
Thomas Kellerer
Date:
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

Re: ordering by date for each ID

From
Samuel Gendler
Date:


On Thu, May 12, 2011 at 1:45 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Nguyen,Diep T wrote on 12.05.2011 03:59:

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



Or the more general answer to your question is this: http://www.postgresql.org/docs/8.4/interactive/functions-window.html  There's lots of neat things you can do with window functions.

--sam