sqlite sorting by WHERE first

Question

I'm trying to sort some data in my android app with sqlite with the query

cursor c = db.query(TABLE_NAME, null, DRILL_TYPE + " IN ('type 1', 'type 2');", null, null, null, SIZE + " ASC", null);

I'm expecting the results to come out sorted by size, regardless of whether the drill is type 1 or 2, but I'm getting all of type 1 sorted, and then I'm getting all of type 2 sorted. What's wrong with my query that's resulting in this?

expected:
type 1     1
type 2     2
type 1     3
type 2     4
type 1     5
type 2     6

actual result:
type 1     1
type 1     3
type 1     5
type 2     2
type 2     4
type 2     6

Edit, after the original question was answered :-

removing the semicolon at the end of the order part fixed things when I use IN, but not when I use NOT IN

I think you can find your answer in [there](https://stackoverflow.com/a/8948476/10915910) Good luck. — Reimulikeaboss, Feb 14 '19 at 04:32

MikeT · Accepted Answer · 2019-02-14T06:49:47.977

I believe the issue may be due to the semi-colon ; at the end of the IN clause. This would effectively mark the end of the SQL and as such result in the ORDER clause being ignored/omitted.

That is try :-

cursor c = db.query(TABLE_NAME, null, DRILL_TYPE + " IN ('type 1', 'type 2')", null, null, null, SIZE + " ASC", null);

In regard to :-

removing the semicolon at the end of the order part fixed things when I use IN, but not when I use NOT IN

Using

 cursor c = db.query(TABLE_NAME, null, DRILL_TYPE + " NOT IN ('type 1', 'type 2')", null, null, null, SIZE + " ASC", null);

would sort the rows, that do not have type 1 or type 2, correctly, consider the following (which replicates your situation but with extra data to show the NOT IN results):-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (drill_type TEXT, size INTEGER);
INSERT INTO mytable VALUES
('type 2',4),('type 1',5),('type 2',6),
    ('type 1',1),('type 2',2),('type 1',3),
    ('type 4',14),('type 3',15),('type 4',16),
    ('type 3',11),('type 4',12),('type 3',13)
;

SELECT * FROM mytable WHERE drill_type IN ('type 1','type 2') ORDER BY size ASC;
SELECT * FROM mytable WHERE drill_type NOT IN ('type 1','type 2') ORDER BY size ASC;
SELECT * FROM mytable WHERE drill_type NOT IN ('type 1','type 2'); ORDER BY size ASC; -- will not sort as expected

Note the above effectively replicates your code

The results would be :-

original solution

using NOT IN correctly

leaving the semi-colon in

That worked! It's always a semi-colon's fault. However, now when I try the statement with NOT IN instead, I get the same problem — vugavich, Feb 14 '19 at 06:09
@vugavich Correctly using `....., " NOT IN ('type 1', 'type 2')", .....` would work. Impossible to know what you are doing wrong for it to not work without code itself. — MikeT, Feb 14 '19 at 06:32

sqlite sorting by WHERE first

Edit, after the original question was answered :-

1 Answers1