How to count the number of rows before a given row in MySQL with CodeIgniter?

Question

Simply put, how can I count how many rows there are before a certain row. I'm using incremental ID's, but rows are deleted a random, so just checking to see what the ID is won't work.

If I have, say, 30 rows, and I've selected one based on a name (or anything really), how many rows are there before that one? It could be 16, 1, 12, or anything.

I'm using MySQL and CodeIgniter.

Answer 1

I assume your primary key column has datatype integer

SELECT COUNT(*) FROM `table`
WHERE id < (SELECT id FROM `table` WHERE `conditions are met for specific row`)