Using the Limit Statement in a SQLite Query

Using the LIMIT statement in a SQLite query

The equals (=) operator is not used with the LIMIT clause. Remove it.

Here's an example LIMIT query:

SELECT column FROM table ORDER BY somethingelse LIMIT 5, 10

Or:

SELECT column FROM table ORDER BY somethingelse LIMIT 10

In your case, the correct statement would be:

return wDb.query(TABELANOME, new String[] {IDTIT, TAREFATIT, SUMARIOTIT}, CONCLUIDOTIT + "=1", null, null, null, null, String.valueOf(limite));

Take a look here at the SQLite select syntax: http://www.sqlite.org/syntaxdiagrams.html#select-stmt

This image is rather useful: http://www.sqlite.org/images/syntax/select-stmt.gif

How can I apply the LIMIT statement in a SQLite query to a specific side of a join?

I'm assuming you are trying to say that you want the results from both tables but only for the first X orders. If so, try this:

SELECT OC.*, O.* FROM OrderContents OC
INNER JOIN  (SELECT * 
             FROM Orders 
             ORDER BY ID 
             LIMIT 2) O ON O.ID=OC.ID

How to use the LIMIT argument in an SQLite Query with Android

Order by id DESC Limit 1:

db.query("table", null, "column=?", new String[]{"value"}, null, null, "id DESC", "1");

Using limit in sqlite SQL statement in combination with order by clause

No. First because the StartTime column may not have UNIQUE constraint. So, even the first query may not always produce the same result - with itself!

Second, even if there are never two rows with same StartTime, the answer is still negative.

The first statement will always order on StartTime and produce the first 10 rows. The second query may produce the same result set but only with a primitive optimizer that doesn't understand that the ORDER BY in the subquery is redundant. And only if the execution plan includes this ordering phase.

The SQLite query optimizer may (at the moment) not be very bright and do just that (no idea really, we'll have to check the source code of SQLite*). So, it may appear that the two queries are producing identical results all the time. Still, it's not a good idea to count on it. You never know what changes will be made in a future version of SQLite.

I think it's not good practice to use LIMIT without ORDER BY, in any DBMS. It may work now, but you never know how long these queries will be used by the application. And you may not be around when SQLite is upgraded or the DBMS is changed.

(*) @Gareth's link provides the execution plan which suggests that current SQLite code is dumb enough to execute the redundant ordering.

SQLite LIMIT OFFSET and WHERE clause

You should never use LIMIT without ORDER BY. Only with ORDER BY is the order in your result set guaranteed and only then LIMIT makes sense.

Moreover you use SUM without a GROUP BY. That gives you one result row. Then you use LIMIT on your results, which is still one result row.

And what is the offset supposed to do? You want to start after the last record in the table? That doesn't seem to make sense.

Here is the query with ORDER BY and SUM after LIMIT:

select sum(id)
from 
(
  select id
  from test1 
  where id % 5 = 0
  order by id desc
  limit 5
) last5;

Using LIKE and LIMIT in sqlite queries in Android

WARNING: You should NOT use string concatenation with the + operator to insert user input in a SQL query.This leaves your app open to a SQL injection attack. I cannot emphasize this enough. Mitigating this common security flaw should be a top priority for all database developers.

Instead, you should use the ? place holder syntax:

String query = "SELECT " + CITIES_NAME +
                    " FROM " + TABLE_CITIES +
                    " WHERE " + CITIES_NAME +
                    " LIKE ?" +
                    " LIMIT 8";
String[] args = {nameLetters + "%%"};
Cursor cursor = database.rawQuery(query, args);

Even if the database is small and only used for your individual app, it is best to make this syntax a habit. Then when you work on larger, more critical databases, you won't have to worry about this issue as much.

This also has the advantage that it quotes the input for you. You completely avoid the error which you encountered that prompted the original question.

Is there any limit on sqlite query size?

Roughly speaking the default limit is 1,000,000 bytes or 1,000,000 characters, so unless your 'students' are over 100,000 characters each your statement should be fine.

The following is taken from http://www.sqlite.org/limits.html

Maximum Length Of An SQL Statement

The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000. You can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824.

If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host parameters for your data. Prepare short SQL statements like this:

INSERT INTO tab1 VALUES(?,?,?); 

Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is also runs faster since the large string does not need to be parsed or copied as much.

The maximum length of an SQL statement can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,size) interface.

EDIT: 26/10/2022 As Tobias explains in his comment, the linked article now depicts the default max length as follows:

Maximum Length Of An SQL Statement
The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.

SQLite: LIMIT values depending on a condition

You can try this:

SELECT ID FROM TABLE WHERE ID < (SELECT MIN(ID) FROM TABLE WHERE (A - B) <= 0) ORDER BY ID

---

Related Topics