0

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
danilonet
  • 1,757
  • 16
  • 33

3 Answers3

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.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • sorry but i need to update column not to obtain an incrementa id – danilonet 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
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.

Community
  • 1
  • 1
alphaneer
  • 184
  • 3
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
user6256515
  • 102
  • 2