I have a field in my mysql database called 'rank'.
I want to run a simple as possible query or php loop that checks if there's an increment starting from 1 in this field across all rows. Doesn't matter the order of the rows, just as long as there's a row with 'rank' of 1, another of 2, another of 3, etc.
I have some code to run if there's a break in the sequence so Im just looking for help on the most resource friendly way of checking.
There needs to be a row with a rank of 1 also.
This will return a row with 0 as the missingrank if rank 1 exists, and any rows in addition to missingrank 0 are missing ranks.
SELECT T.ID - 1 AS [MISSINGRANK]
FROM URTABLE T
LEFT JOIN URTABLE T2 ON T.rank = T2.rank + 1
WHERE T2.ID IS NULL
take a look to this:
mysql> set @a:=1;
mysql> select actor_id,if(@a<>actor_id,'break',@a),@a:=@a+1 from t2 order by actor_id;
+----------+-----------------------------+----------+
| actor_id | if(@a<>actor_id,'break',@a) | @a:=@a+1 |
+----------+-----------------------------+----------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 5 |
| 5 | 5 | 6 |
| 6 | 6 | 7 |
| 7 | 7 | 8 |
.....
| 99 | 99 | 100 |
| 101 | break | 101 |
| 102 | break | 102 |
| 103 | break | 103 |
| 104 | break | 104 |
| 105 | break | 105 |
+----------+-----------------------------+----------+
207 rows in set (0.00 sec)
I don't know if it's what you want to (I tried in local host), where breaks mean that the actor_id has broken the auto_auto_increment, you can add group by , order by, etc, whatever you need