Skip site navigation (1) Skip section navigation (2)

Re: Updating a table field with a consecutive number

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Updating a table field with a consecutive number
Date: 2011-02-19 16:13:34
Message-ID: ijoq7d$dd1$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Thomas KellererDate: 2011-02-19 16:14:18
Subject: Re: Combining queries
Previous:From: Sean DavisDate: 2011-02-19 13:47:15
Subject: Re: Combining queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group