Create MySQL trigger that updates position of each row when sorted by a specific field Let's see an example, I've got this table that contains teams, each of them having a certain number of points:

CREATE TABLE teams (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  points INT(11) NOT NULL,
  position INT(11),
  PRIMARY KEY (id)
);


In the position field, I want to have their current position in comparison with other teams, so for example I've got 3 teams, sorted by the position:

id  name   points  position
2   team1  1700    1
3   team2  1500    2
1   team3  1300    3


Every time teams table is updated, I want to update the position. For example, I update the team2 with 500 points so it has 2000 points now and is first:

id  name   points  position
3   team2  2000    1
2   team1  1700    2
1   team3  1300    3


So how do I update position field every time points are updated? My idea was to create a trigger as follows:

delimiter |

CREATE TRIGGER updatePoints AFTER UPDATE ON teams
  # How to go over each row (not only updated rows) and change each row's position (rank) when teams are sorted by points?
  BEGIN
    # And here code to update the points

  END;
|

delimiter ;


However I have no idea how to create a trigger that will update positions correctly when the table is sorted by points.

EDIT: The idea is to create a trigger that will update the team by assigning correct position to each team in order to be able to retrieve the position of a specific team just by doing:

SELECT position FROM teams WHERE id = 1
You (as in, the user) won't generally sort data within a table.  You (as in, a user) will sort the output from queries.

The order that the data is stored is generally irrelevant to you, and the database will store the data in a way that makes sense for it.

If you want/need the data ordered when you run a query, put the order by statement in the query...

select x,y,z 
from table1
order by x asc, y desc