MySQL update statement to store ranking positions

by

Here’s a way to do it:

SET @r=0;
UPDATE table SET Ranking= @r:= (@r+1) ORDER BY Score DESC;

/* use this if you just want to pull it from the db, but don't update anything */
SET @r=0;
SELECT *, @r:= (@r+1) as Ranking FROM table ORDER BY Score DESC;

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. MySQL, update multiple tables with one query
  3. UPDATE multiple rows with different values in one query in MySQL
  4. Multiple Updates in MySQL
  5. mysql update column with value from another table
  6. update multiple rows using limit in mysql?
  7. MySQL/SQL: Update with correlated subquery from the updated table itself
  8. What are differences between INSERT and UPDATE in MySQL?
  9. MySql – Way to update portion of a string?
  10. MySQL UPDATE syntax with multiple tables using WHERE clause
  11. The best way to remove value from SET field?
  12. Update MySQL with if condition
  13. Update multiple rows with multiple ‘where’ clauses for each individual row
  14. How can I tell when a MySQL table was last updated?
  15. MySQL DELETE FROM with subquery as condition
  16. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  17. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  18. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  19. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  20. Determine Rank based on Multiple Columns in MySQL
  21. Checking multiple columns for one value
  22. What is better in MYSQL count(*) or count(1)?
  23. How to get the count of each distinct value in a column? [duplicate]
  24. How to enable INNODB in mysql
  25. Bash script to insert values in MySQL
  26. MySQL GROUP BY DateTime +/- 3 seconds
  27. How do I lag columns in MySQL?
  28. How to get multiple records against one record based on relation?
  29. SQL query to check if a name begins and ends with a vowel
  30. How to use a user variables in MySQL LIKE clause?

Leave a Comment