Обсуждение: Updating a table field with a consecutive number
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
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 assignfrom 1 to n. I suppose I need to traverse the table ordered by date and score (one record at a time) and UPDATE theposition field starting with 1 until the date changes. At this point, I would start from position 1 again until the nextdate change and so on. At least this is what I imagine I can do. I will appreciate any advice about a way of achievingmy 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 areon 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