Re: [SQL] ordering by date for each ID
On Thu, May 12, 2011 at 1:45 PM, Thomas Kellerer 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
Re: [SQL] ordering by date for each ID
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] ordering by date for each ID
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql