title: SQLite - Get a specific row index for a Sorted/Filtered Query
body: <p>I'm creating a caching system to take data from an SQLite database table using a sorted/filtered query and display it.  The tables I'm pulling from can be potentially very large and, of course, I need to minimize impact on memory by only retaining a maximum number of rows in memory at any given time.  This is easily done by using <code>LIMIT</code> and <code>OFFSET</code> to load only the records I need and update the cache as needed.  Implementing this is trivial.  The problem I'm having is determining where the insertion index is for a new record inserted into a particular query so I can update my UI appropriately.  Is there an easy way to do this?  So far the ideas I've had are:</p>
  
  <ol>
  <li>Dump the entire cache, re-count the Query results (there's no guarantee the new row will be included), refresh the cache and refresh the entire UI.  I hope it's obvious why that's not really desirable.</li>
  <li>Use my own algorithm to determine whether the new row is included in the current query, if it is included in the current cached results and at what index it should be inserted into if it's within the current cached scope.  The biggest downfall of this approach is it's complexity and the risk that my own sorting/filtering algorithm won't match SQLite's.  </li>
  </ol>
  
  <p>Of course, what I want is to be able to ask SQLite: Given 'Query A' what is the index of 'Row B', without loading the entire query results.  However, so far I haven't been able to find a way to do this.</p>
  
  <p>I don't think it matters but this is all occurring on an iOS device, using the objective-c programming language.</p>
  
  <p><em><strong>More Info</em></strong></p>
  
  <p>The Query and subsequent cache is based off of user input.  Essentially the user can re-sort and filter (or search) to alter the results they're seeing.  My reticence in simply recreating the cache on insertions (and edits, actually) is to provide a 'smoother' UI experience.</p>
  
  <p>I should point out that I'm leaning toward option "2" at the moment.  I played around with creating my own caching/indexing system by loading all the records in a table and performing the sort/filter in memory using my own algorithms.  So much of the code needed to determine whether and/or where a particular record is in the cache is already there, so I'm slightly predisposed to use it.  The danger lies in having a cache that doesn't match the underlying query.  If I include a record in the cache that the query wouldn't return, I'll be in trouble and probably crash.</p>