I am trying to figure out how to calculate the rankings for a game of assassins that I am running, I wish to rank people by kills primarily, and then by time of kills (those who got kills before the others are ranked higher) and then last the people that have been assassinated already ranked below those that are alive.
My table for logging assassinations looks like this:
mysql> describe assassinations;
+-----------+-----------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| assassin | int(11) | NO | | NULL | |
| target | int(11) | NO | | NULL | |
| timestamp | int(11) | NO | | NULL | |
| ver | enum('assassin','target','both','none') | NO | | none | |
| confirmed | bit(1) | NO | | b'0' | |
+-----------+-----------------------------------------+------+-----+---------+----------------+
I am thinking that there must be a way to order the mysql results just like the way I want it to be ranked, but I don't know how. I got as far as trying to get the most common assassin value :(. I am using PHP with MySQL so a PHP solution would also work. (Please note, ignore the "confirmed" field, but "ver" must be both for it to be a valid kill).
Any help would be much appreciated. :)
Use COUNT and MIN to get the number of kills and the time of the first kill. And an EXISTS subquery to tell if the assassin has already been killed. Then you can use all these values in the ORDER BY clause to rank the players.
SELECT a1.assassin, COUNT(*) AS kills, MIN(timestamp) AS killtime,
EXISTS (SELECT * FROM assassinations AS a2
WHERE a2.target = a1.assassin) AS killed
FROM assassins AS a1
WHERE ver = 'both'
GROUP BY assassin
ORDER BY kills DESC, killtime ASC, killed ASC