Is there any way to select a row by its row number. An auto_incremented field won't do because its consistency is broken after some rows are deleted.
What I want to code is a kind of paged-results, but a bit more user-friendly.
Lets consider we have a table of 26 rows with letters from 'a' to 'z' in a 'title' column. A common paging would look something like this: 1-10 11-20 21-26
What I want to get is: a-j k-t u-z
It would be nice to use one simple SQL-query like this:
But the problem is there is no anyting like RECNO() function in MySQL Well, at least, I haven't found one.
SELECT RECNO(), title FROM table WHERE MOD(RECNO(),10) IN (0,9)
Of course I can always use a loop of LIMIT-ed queries to get the titles I need one at a time (in fact, two at a time ). But in case of 1000-rows table it would require about 100 queries.
Second solution I see is to add a 'row_number' column to the table, and renumerate it after deletes to keep it consistent. It seems a better solution because deletion (and addition) of rows would be rather rare.
So, the question is if there is some MySQL-function (or some tricky hidden column?) that return the current row number. If not, what would be the best way to code such an alphabethic result paging?