0

I'm trying sort all the users in the database by a value and give them a new ID, that will act as their position.

enter image description here

I've tried to order them descending by money, that's the value I'm looking for to order by, but I wasn't able to properly update each user's ID in order.

Please note, the database has over 5000 entries, so I need a way that won't lag out the database.

SandPiper
  • 2,816
  • 5
  • 30
  • 52
Zeta Reactor
  • 85
  • 1
  • 7
  • 1
    Does everyone have a different value of money? If not, how do you want to sort X people who all have the same amount of money? – Caius Jard Dec 31 '18 at 16:04
  • You want to basically replace the id column with a row number? This doesn't sound like a very good idea... – Andrew Dec 31 '18 at 16:10
  • The money value varies, there can be people that will have the same ammount of money at a time. The goal is to get the ID or another Column to have the row's number after the sorting. – Zeta Reactor Dec 31 '18 at 16:56
  • Possible duplicate of [Rank function in MySQL](https://stackoverflow.com/questions/3333665/rank-function-in-mysql) – GMB Dec 31 '18 at 18:34

1 Answers1

0

Unfortunately, MySQL doesn't have a nice easy RANK() function. You can use a workaround like what is described here though: Rank function in MySQL

SELECT t1.*, @curRank := @curRank + 1 AS rank
FROM table_name t1, (
SELECT @curRank := 0
) t2
ORDER BY money
SandPiper
  • 2,816
  • 5
  • 30
  • 52