update column with incremental value

Question

i have a table like this

userid | points | position
1      | 100    | NULL
2      | 89     | NULL
3      | 107    | NULL

i need a query for update the position column ordering by points desc, example result:

userid | points | position
1      | 100    | 2
2      | 89     | 3
3      | 107    | 1

Have you tried anything? Like taking the name of your post and putting it into Google with SQL in front of it? — dfundako, May 11 '16 at 17:16

D Stanley · Accepted Answer · 2016-05-11T17:49:56.327

3

I would not use physical columns that depend on values in other rows, otherwise you have to update the entire table every time one row changes. Use a view or other mechanism to calculate the position on the fly.

The query to calculate "position" would look something like:

SELECT 
    userid, 
    points, 
    RANK() OVER (ORDER BY points DESC) AS position

However, if you have to make it an UPDATE then you could use something like

UPDATE a
SET a.position = b.position
FROM {table_name} a
INNER JOIN
(
    SELECT 
        userid, 
        RANK() OVER (ORDER BY points DESC) AS position
        FROM {table_name}
) b
ON a.userid = b.userid

but keep in mind that you will need to run the update every time the table is updated, so performance may be an issue if it's a decent size table that gets updated a lot.

edited May 11 '16 at 17:49

answered May 11 '16 at 17:16

D Stanley

144,385
11
166
231

sorry but i need to update column not to obtain an incrementa id – Danilo Calzetta May 11 '16 at 17:25
1

Then use the same philosophy with an update query. By the way, given that this answer does not have anything to do with incremental ids, are you sure you actually understand it? – Dan Bracuk May 11 '16 at 17:28
@DaniloCalzetta I added a potential `UPDATE` query with the same caveats. – D Stanley May 11 '16 at 17:50

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

Also consider using DENSE_RANK() instead of RANK() when you want to increment the ranking of your 'position' by 1 as the 'points' change. RANK() will do what you want, though it will create number sequence gaps according to how many duplicate 'userids' are equal in 'points' standing's (if that's ever the case in your spec).

Refer to this answer for the difference between them.

edited May 23 '17 at 12:23

Community

1
1

answered May 11 '16 at 18:30

alphaneer

174
3

score 0 · Answer 3 · answered May 11 '16 at 17:50

0

You can do something like this:

UPDATE t
SET position = t2.position
FROM table t
JOIN (
    SELECT 
    userid, 
    points, 
    RANK() OVER (ORDER BY points DESC) AS position
    FROM table) t2 ON t2.userid = t.userid

answered May 11 '16 at 17:50

user6256515

102
2

update column with incremental value

3 Answers3