How To Select Rows by Row Number in Sqlite

Introduction

My situation is implement a Lazy Load function for Data Grid, which has to deal with a lot of data (10 millions rows).

To save memory, I'm only loading a maximum of 3 pages to display on the DataGrid.

The 1^st problem is "HOW TO KEEP TRACK OF THE SELECTION OVER ALL PAGES USER VIEWED".

So I keep the selection as the following structure:

+ List<RowSelection>
- StartIndex
- EndIndex

My application uses Sqlite Database and now is REAL PROBLEMS.

SELECT ROWS BY ITS INDEX WITH COMPLICATED SEARCH CONDITIONS/SORT ORDER

After a few days, finally I solved my problem. And this is how I solved it.

Background

• We need Mapping Rows Index with Row IDs (Primary Key) with respect to Search Conditions / Sort Order
• Write a User Define Function (UDF) for Sqlite to Remap from Row IDs to Row Indexes in your Query
• Build a WHERE clause on Row Ids based on Mapped Row IDs & Selected Row Index

Using the Code

• Write UDF SqliteFunction FNROWNUM:
  C#

  [SQLiteFunction(Name = "FNROWNUM", Arguments = 1, FuncType = FunctionType.Scalar)]
  class FNROWNUM : SQLiteFunction
  {
      public static List<int> mappedRowId = new List<int>();
      public override object Invoke(object[] args)
      {
          int rowId = int.Parse(args[0].ToString());
          if (mappedRowId.Contains(rowId)) return mappedRowId.IndexOf(rowId);
  
          return -1;
      }
  }

• Mapping Rows Index with Row Ids:
  C#

  sql = "SELECT w.localId FROM " +
      "(SELECT * FROM words) AS w " +
      "LEFT JOIN (SELECT * FROM WordType) wt ON w.type = wt.id " +
      "ORDER BY words DESC";
  
  SQLiteCommand cmd = new SQLiteCommand(sql, cnn);
  var reader = cmd.ExecuteReader();
  
  FNROWNUM.mappedRowId = new List<int>();
  while (reader.Read())
  {
      var localId = int.Parse(reader.GetValue(0).ToString());
      FNROWNUM.mappedRowId.Add(localId);
  }

• Now, FNROWNUM.mappedRowId contains all Ids ordered as we want.
• Now, we get the rows data with the:
  C#

  //This is SELECTED ROW INDEXs which we want to Query on Data
  var rowNumbers = new List<int> {1, 3, 5};
  //Use mappedRowId => We get the Row IDs of Selected Rows
  var rowIds = rowNumbers.Select(i => FNROWNUM.mappedRowId.ElementAt(i));
      
  //And query data using Row IDs (which always work with any Search Conditions / Sort Order)
  sql = "SELECT w.*, FNROWNUM(w.localId) AS ROWNUM FROM " +
      "(SELECT * FROM words " +
      "WHERE localId IN (" + String.Join(", ", rowIds) + ")) AS w " +
      "LEFT JOIN (SELECT * FROM WordType) wt ON w.type = wt.id " +
      "ORDER BY words DESC";
  
  cmd = new SQLiteCommand(sql, cnn);
  reader = cmd.ExecuteReader();

• In the second query, I also get the ROWNUM by using the UDF FNROWNUM. This function also uses the mappedRowId to return the correct Row Index.
• This is all data ordered by Words DESCENDING.

  

• This is Result Data WHEN selecting ROW 1, 3, 5 as the sample above.

  

Points of Interest

I tried few other solutions by Googling:

Solution 1: Their use query is like this:

SELECT COUNT(SELECT Words FROM Words WHERE Words > w.Words) , * FROM Words w ORDER BY Words DESC

This could be work but it takes the COST OF PERFORMANCE by recursively COUNTing the previous rows. And it will be very complicated SQL if we have a lot of Search Conditions / Sort Order.

Solution 2: Use temp table to store the Filtered/Ordered data, then extract the rows by Selected Index.

This is better than Solution 1, but WHAT IF YOU GOT 10 Millions Records ?! STORAGE PROBLEM

History

• Created on 06/09/2015