1

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
  • Have you thought how efficient this is going to be? Think about when the table will have 100M rows and one - a single - row is updated. The whole 100M rows would have to be updated as well. – ypercubeᵀᴹ Feb 8 '16 at 11:49
  • Table will have maximum of 10K rows. The update operation on team points happens less often than displaying the position of the team, so updating the rows seems more efficient than counting the position of the team every time a team is displayed. But I may be wrong, if you have suggestion how to do this better, please provide. – emil.c Feb 8 '16 at 13:43
  • The database can cache queries especially if the data doesn't change. – JMD Coalesce Feb 9 '16 at 18:02
  • Why not just use an index? – JMD Coalesce Feb 9 '16 at 18:06
  • What do you mean by using index? – emil.c Feb 9 '16 at 18:54
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
  • The problem is that I want to retrieve a specific team and its position from the database. So If I do a select, it will give me the team I want, but how do I get its current position when sorted by the points? – emil.c Feb 8 '16 at 13:53
  • 1
    Hm, you've edited that question since I answered so now my answer doesn't make much sense. Try searching for "MySQL rank function". For a REALLY simple example, see sqlfiddle.com/#!9/93116/3/0 – Phil Sumner Feb 9 '16 at 9:10
  • Thanks for the example, I did search for rank functions, that is also the reason why I edited my question as I realised it's totally wrong to use FOR EACH ROW. Therefore the question is not really how to use FOR EACH ROW that has been updated, but how to go over every row and for each row set the rank appropriately. I have also realised that this may have a terrible impact on performance so I'm still considering if this is the right solution to use the trigger. – emil.c Feb 9 '16 at 10:04
  • I would seriously suggest rethinking your question. Start with the thing that gets forgotten a lot: What are the requirements of this solution? Once you've got that, the technical solutions become easier to define. – Phil Sumner Feb 9 '16 at 11:49
  • I rethought my question and realised I rather use a helper method in php that will give me a rank for a team with provided id as a parameter. The method uses similar query to what you have provided to retrive the rank. The real requirement though is to be able to immediately retrieve for example 10 teams with their overall rank, however those teams are also filtered by another field so I can't just say the second retrieved team ordered by points has rank 2. – emil.c Feb 9 '16 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.