SQLite on Android: Union with limits alternative

Question

I want to create a Cursor, which later I will pass via CursorAdapter to AutoCompleteTextView for autocompletion suggestions. The problem is that my SQL statement needs to select 2 different result sets from the same table, just sorted by different criteria. At the same time these result sets must not overlap - otherwise I would have multiple same suggestions. On MySQL, this would be quite easy:

(SELECT field3 FROM table ORDER BY field1 ASC LIMIT 5)
UNION
(SELECT field3 FROM table ORDER BY field2 DESC LIMIT 10)
LIMIT 10

In this case I would have always up to 10 unique values. On SQLite, unfortunately, this is not the case - I cannot have limits on particular selects, just on the whole result set, which makes then UNION not useful for this case at all...

To solve this problem I've thought about making 2 queries separately: iterating through Cursor from 1st query and putting values into an Array, then iterating over Cursor from 2nd query and also putting values into the same array while checking and skipping already existing values in Array to avoid the duplicates. Then use ArrayAdapter to pass values to AutoCompleteTextView.

Theoretically this would work - but what I don't like that everything becomes more complex and much more code to write to solve such a simple task...

Is there maybe a better and easier way to do it?

Answer 1

Have you considered doing an artificial intelligence limit, to select from the middle of the recordset. Let me present the idea in an example

First you want to select from the first query the top 5 records. Make the order inversed so it shows up in the end of the recordset. No limit applied here.

SELECT field3 FROM table ORDER BY field1 DESC

Example:

field1 | field2
12       x
11       x
9        x 
8        x
7        x
6        x
5        x
4        x
3        x
2        x
1        x

Then do the second query in the order you want. No limit applied here.

SELECT field3 FROM table ORDER BY field2 DESC

Then do an union on them

(SELECT field3 FROM table ORDER BY field1 DESC)
UNION
(SELECT field3 FROM table ORDER BY field2 DESC)

The results you want are in the middle of the resultset

field1 | field2
11       x
10       x
9        x 
8        x
7        x
6        x
----------
5        x
4        x
3        x
2        x
1        x
x        1
x        2
x        3 
x        4
x        5
x        6
x        7
x        8
x        9
x        10
----------
x        11

Now you can apply an offset and limit to your query. In order to this work, you need to query prior the number of records in your table that are eligible for the query to work.

The final query would be something like:

(SELECT field3 FROM table ORDER BY field1 DESC)
UNION
(SELECT field3 FROM table ORDER BY field2 DESC)
LIMIT 5+10 OFFSET N-5

I wrote the LIMITS explicitly to be able to calculate it yourself, offset N-5 means to skip N-5 records from the beginning of the recordset.

Answer 2

The following will give you the same results as your MySQL example:

CREATE TABLE IF NOT EXISTS unionTemp ( field3 TEXT)
DELETE FROM unionTemp

INSERT INTO unionTemp SELECT field3 FROM table ORDER BY field1 ASC LIMIT 5
INSERT INTO unionTemp SELECT field3 FROM table ORDER BY field2 ASC LIMIT 10

SELECT field3 FROM unionTemp GROUP BY field3 LIMIT 10

You may want to use a temporary table. (CREATE TEMP TABLE...)