how to get rowNum like column in sqlite IPHONE

Question

I have an Sqlite database table like this (with out ascending)

But i need to retrive the table in Ascending order by name, when i set it ascending order the rowId changes as follows in jumbled order

But i need to retrieve some limited number of contacts 5 in ascending order every time

like Aaa - Eeee and then Ffff- Jjjjj ......

but to se**t limits like 0-5 5-10 .... ** it can able using rowids since they are in jumble order

So i need another column like (rowNum in oracle) wich is in order 1234567... every time as follows

how to retrive that column with existing columns

Note: WE DONTE HAVE ROWNUM LIKE COLUMN IN SQLITE

Answer 1

The fake rownum solution is clever, but I am afraid it doesn't scale well (for complex query you have to join and count on each row the number of row before current row).

I would consider using create table tmp as select /*your query*/. because in the case of a create as select operation the rowid created when inserting the rows is exactly what would be the rownum (a counter). It is specified by the SQLite doc.

Once the initial query has been inserted, you only need to query the tmp table:

select rowid, /* your columns */ from tmp
order by rowid