Simon et al, On Sun, Nov 10, 2013 at 7:18 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 10 Nov 2013, at 3:05pm, Igor Tandetnik <i...@tandetnik.org> wrote: > >> That rather depends on what value you deem to be the correct one. You've >> never explained the desired outcome of all this choreography. > > He's trying to keep each player's rank in his league table. And he wants the > rank column for all players to be updated each time he inserts a new player > in the table, so if he inserts a new player who is fourth, all players that > were fourth or after fourth move down one rank.
This is what I'm looking for. Consider the players table data: id player_name rank 1 PlayerA 1 2 PlayerB 2 3 PlayerC 3 4 PlayerD 4 Now when the new league is created I specify that only PlayerB and PlayerC should be in there. And so I'm writing the following query: INSERT INTO playersinleague() SELECT * FROM players ORDER BY rank; Now in playersinleague table I'd like to see following: id current_rank original_rank 2 1 1 3 2 2 So that rank in playersinleague table should start from 1 and be incremented by 1. > > Once again I suggest using a TRIGGER is the wrong way to do it: > > A) You have to write a trigger for when a player joins a league. > B) You have to write an equivalent trigger for when a player leaves a league. > C) You have to write an equivalent trigger for when a player moves position. > D) That's a lot of checking and operations any time any player does anything. Not really. I'm updating the table when the program exit. And I already have this logic. It does not require to write any triggers. But because the ranks in the main pool (players table) are not ordered the same way as they could be added to the league (playersinleague table) it can be updated with the trigger. I hope I explained everything and it's now clearer. Now when the league is created there is no records and, therefore, max(current_rank) is NULL and so "1 + max(current_rank)" does evaluate to NULL. Now, I would expect for the max() function in this case to be evaluated to 0 and not NULL. This is plain mathematics: max value of nothing is nothing which mathematically 0. Which means that I can use this value in mathematical expressions. Thank you. > > Instead I think it makes more sense just to deduce the ranks from the results > of your SELECT, when you select the players in points order. The player who > comes off first is ranked 1. Not only is it simple but it requires no extra > operations, so it speeds up all INSERT/UPDATE/DELETE. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
