Обсуждение: Updating a table field with a consecutive number

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