how to get rowNum like column in sqlite IPHONE

ghz 1years ago ⋅ 2858 views

Question

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

enter image description here

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

enter image description here

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

enter image description here

how to retrive that column with existing columns

Note: WE DONTE HAVE ROWNUM LIKE COLUMN IN SQLITE

Answer

This is a way of faking a RowNum, hope it helps:

SELECT 
   (SELECT COUNT(*)
    FROM Names AS t2
    WHERE t2.name < t1.name
   ) + (
      SELECT COUNT(*) 
      FROM Names AS t3 
      WHERE t3.name = t1.name AND t3.id < t1.id
   ) AS rowNum,
   id,
   name
FROM Names t1
ORDER BY t1.name ASC

SQL Fiddle example

#oracle