4

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

user1811427
  • 359
  • 2
  • 6
  • 17
  • 1
    Should be easy to generate a row number in Objective-C? – Andomar Dec 24 '12 at 15:48
  • SELECT rowid, name, (SELECT COUNT(*) FROM contactinfo AS t2 WHERE t2.name <= t1.name) AS rowNum FROM contactinfo t1 where rownum >10 ORDER BY t1.name asc – user1811427 Dec 24 '12 at 16:07

3 Answers3

17

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
yves
  • 664
  • 1
  • 12
  • 24
6

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

Community
  • 1
  • 1
chue x
  • 18,573
  • 7
  • 56
  • 70
2

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

ErikE
  • 48,881
  • 23
  • 151
  • 196
ederbf
  • 1,713
  • 1
  • 13
  • 18