paging in sqlite table using c#

Question

I'm trying to page through this table so I ended up with this query (which is not working).

SELECT MIN(user_uid) AS FIRST,
MAX(user_uid) AS LAST,
user_number, user_name
FROM user_table
WHERE user_uid > MAX(FIRST,{0}) AND user_uid < MIN(LAST,{1})
AND ( user_name LIKE '%{2}%' OR user_number LIKE '%{3}%' )
AND user_category={4} OR user_category={5}
ORDER BY user_uid LIMIT {6}

I'm getting parameters' values from my c# variables. I'm storing FIRST, LAST externally to use them for moving to next/previous pages.

I created this index too:

CREATE INDEX idx1 ON user_table (user_uid);

please note: I saw this answer, but this is the only way I thought about to get First and Last values.

How to page in this table efficiently?

This query does not make sense. What values do you intend to use for `{0}` and `{1}` on the first page, and on the next page? — CL., Aug 10 '14 at 15:15
The first and last value are the values in the first and last returned row. — CL., Aug 10 '14 at 17:45

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Your query isn't working because Min(Column) and Max(Column) in SQL returns an evaluation (aggregate) of all rows so doesn't know what value to have for every row in the non-aggregate query in your question.

If you really need the min and max you could return them in separate select statements so your dataset has 3 tables (if you're using ADO).

However paging should generally be achieved using offset as in the case below (note that the order by should make the query deterministic so it returns the same results every time).

SELECT
user_number, 
user_name
FROM user_table
WHERE ( user_name LIKE '%{1}%' OR user_number LIKE '%{2}%' )
AND user_category={3} OR user_category={4}
ORDER BY user_uid LIMIT {5} OFFSET {6}

Where {5} is page size and {6} is page number * page size.

Sorry if this doesn't answer your question, it's not clear what you want FIRST and LAST for. Page number and size are generally kept track off by the UI. Additionally, I haven't tested the above in SQLite.

Note

The code you've posted above looks vulnerable to SQL injection if you're using something like String.Format() to build your query. Consider changing this.

paging in sqlite table using c#

1 Answers1

Note