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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2011-02-19 16:14:18 | Re: Combining queries |
Previous Message | Sean Davis | 2011-02-19 13:47:15 | Re: Combining queries |