pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedordering by date for each ID
3+ messages / 3 participants
[nested] [flat]
* ordering by date for each ID
@ 2011-05-12 01:59 Nguyen,Diep T <diep@ufl.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Nguyen,Diep T @ 2011-05-12 01:59 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ordering by date for each ID
@ 2011-05-12 20:45 Thomas Kellerer <spam_eater@gmx.net>
parent: Nguyen,Diep T <diep@ufl.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2011-05-12 20:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ordering by date for each ID
@ 2011-05-12 22:18 Samuel Gendler <sgendler@ideasculptor.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Samuel Gendler @ 2011-05-12 22:18 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-12 22:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-12 01:59 ordering by date for each ID Nguyen,Diep T <diep@ufl.edu>
2011-05-12 20:45 ` Thomas Kellerer <spam_eater@gmx.net>
2011-05-12 22:18 ` Samuel Gendler <sgendler@ideasculptor.com>
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