4

I've got a sqlite db with 100K rows. However, some of these rows have been deleted over time, so that the primary keys are no longer sequential. Now, they are ordered like 3,5,6,9,900,n...

The problem is that I want to put these in a UITableView with the cellForIndexRowAtIndexPath being 0,1,2,3 etc. Is there a way I can do something like 

SELECT * FROM table WHERE id =  0;

but rather than have id = 0, I want to select the index position at 0 like;

SELECT * FROM table where table._table_index = 0; //don't try this at home

Thanks!

4m1r
  • 12,234
  • 9
  • 46
  • 58
  • Possible duplication of http://stackoverflow.com/questions/8976925/select-statement-in-sqlite-recognizing-row-number – Moop Jul 25 '14 at 00:38

1 Answers1

8

You can use the LIMIT and OFFSET keywords to select a single record at a specific location in your result set.

EX: This statement will return one row at index position 10

SELECT * FROM Table LIMIT 1 OFFSET 10;

note: since answering the question SQLite has added window functions. With window functions, it is possible to return the row number of a record in a query. 

SELECT 
row_number() OVER (ORDER BY FirstName, LastName) AS rowNum,
FirstName, 
LastName,
FROM People;

Window Functions were added in version sqlite 3.25

  • System.Data.Sqlite - 1.0.110.0 or newer
  • Microsoft.Data.Sqlite - 2.2.2 or newer
BenCamps
  • 1,694
  • 15
  • 25