Selecting rows by row number

[simik]

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:

SELECT RECNO(), title FROM table WHERE MOD(RECNO(),10) IN (0,9)

But the problem is there is no anyting like RECNO() function in MySQL Well, at least, I haven't found one.

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?

[kleineme]

Hi,

you don't have to loop through various LIMITs, you may LIMIT the result set from any starting point:

SELECT * FROM tbl LIMIT 45, 10

45 is the offset (in fact it's the 46th row), and it would then return 10 rows from there

[simik]

kleineme,
What I need to get from the table are titles number 0, 9, 10, 19, 20, 29, 30, 39, etc. So I'll have to use a loop of SQL-queries. Something like this:

for ($i = 9; $i < NUMBER_OF_ROWS_IN_TABLE; $i += 10) {
    mysql_query("SELECT title FROM table LIMIT $i, 2");
    ...
} 

[r937]

to ensure that you get reliable, consistent, and repeatable results, make sure you include an ORDER BY clause in that query