SQLite3 and limiting the number of results

Question

Is there a clean way to limit the number of hits from an SQLite3 SELECT statement?

For example, I might query with SELECT * FROM myTable WHERE name='Smith'; realising I could encounter thousands of hits. I'd like SQLite3 to give me say the first 10 it encounters and then terminate the query. How do I do this?

If SQLite3 does not provide this immediately, is there anything I can edit in the SQLite3 source code from which I can rebuild?

Assume I'm in an environment where I have only one thread and I'd like control back in a reasonable time.

Answer 1

You're looking for the LIMIT clause:

SELECT * FROM myTable WHERE name='Smith' LIMIT 10

Answer 2

See the SELECT syntax: there is a LIMIT keyword:

select * from sometable where .... limit 10;

Look at the OFFSET too, can be helpful for paging results. (Also these are often combined with an ORDER BY clause if you want consistent results across queries.)

Answer 3

From the SQLite docs:

The LIMIT clause is used to place an upper bound on the number of rows returned by a SELECT statement. Any scalar expression may be used in the LIMIT clause, so long as it evaluates to an integer or a value that can be losslessly converted to an integer. If the expression evaluates to a NULL value or any other value that cannot be losslessly converted to an integer, an error is returned. If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned. Otherwise, the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to. Or, if the SELECT statement would return less than N rows without a LIMIT clause, then the entire result set is returned.