CODEWITHSUNDEEP
sqlitesql-order-by
CLucera
CLucera

Reputation: 1201

sqlite query hangs when adding ORDER BY

I'm getting some data from a sqlite database, my query works fine until i add the order by clause

my query is structured like this

SELECT * FROM TESTI WHERE TESTO LIKE '%test%' COLLATE NOCASE LIMIT 100 OFFSET 0

this run fine and i get all the record containing the word test on the first 100 row of the database, but when i add the ORDER BY clause in this way

SELECT * FROM TESTI WHERE TESTO LIKE '%test%' COLLATE NOCASE LIMIT 100 OFFSET 0 ORDER BY ID

the system hangs for an indefinite amount of time (i have to force close)

the Table TESTI has 35000 rows how can i order by id without hang all the system?

Upvotes: 2

Views: 524

Answers (1)

laalto
laalto

Reputation: 152907

You have an SQL syntax error. ORDER BY should come before LIMIT and OFFSET, like this:

SELECT * FROM TESTI WHERE TESTO LIKE '%test%' COLLATE NOCASE ORDER BY ID LIMIT 100 OFFSET 0

Why it hangs is not due to the SQL but your program running it. Likely it cannot recover from a syntax error.

Upvotes: 2

Related Questions