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

Search Discussions

  • Thomas Kellerer at Feb 19, 2011 at 4:13 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
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