JORGE MALDONADO wrote on 19.02.2011 02:06:
Let's suppose I have a table like this one but without data in the Position field:
----------------------------------------------------------------
Date Score Position
----------------------------------------------------------------
Jan. 2, 2011 1000 1
Jan. 2, 2011 999 2
Jan. 3, 2011 1000 1
Jan. 3, 2011 999 2
Jan. 3, 2011 998 3
Jan. 4, 2011 1000 1
Jan. 4, 2011 999 2
Jan. 4, 2011 998 3
Jan. 4, 2011 997 4
As you can see, the records are order by date and each date has a score and, depending on the score, a position is assign from 1 to n. I suppose I need to traverse the table ordered by date and score (one record at a time) and UPDATE the position field starting with 1 until the date changes. At this point, I would start from position 1 again until the next date change and so on. At least this is what I imagine I can do. I will appreciate any advice about a way of achieving my objective.
Respectfully,
Jorge Maldonado
You don't necessarily need to update the table, you can simply retrieve this position during retrieval (at least if you are on 8.4 or later):


SELECT date,
score,
row_number() over (partition by date order by score desc) as position
FROM your_table
ORDER BY 1,3;

The same can of course be used in an update statement, but it's usually better to not store information that can be "calculated" when retrieving the data.

Regards
Thomas

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 19, '11 at 1:06a
activeFeb 19, '11 at 4:13p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase