how to get rowNum like column in sqlite IPHONE

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

You can use offset/limit.

Get the first, 2nd, and 3rd groups of five rows:

select rowid, name from contactinfo order by name limit 0, 5
select rowid, name from contactinfo order by name limit 5, 5
select rowid, name from contactinfo order by name limit 10, 5

Warning, using the above syntax requires SQLite to read through all prior records in sorted order. So to get the 10th record for statement number 3 above SQLite needs to read the first 9 records. If you have a large number of records this can be problematic from a performance standpoint.

More info on limit/ offset:

Sqlite Query Optimization (using Limit and Offset)

Sqlite LIMIT / OFFSET query