SQLite3 (or general SQL) retrieve nth row of a query result

Question

Hi All, quicky question on SQLite3 (may as well be general SQLite)

How can one retrieve the nth row of a query result?

row_id (or whichever index) won't work on my case, given that the tables contain a column with a number. Based on some data, the query needs the data unsorted or sorted by asc/desc criteria.

But I may need to quickly retrieve, say, rows 2 & 5 of the results.

So other than implementing a sqlite3_step()==SQLITE_ROW with a counter right now I have no idea on how to proceed with this.

And I don't like this solution too much because of performance issues. so if anyone can drop a hint that'd be highly appreciated.

Regards david

Answer 1

add LIMIT 1 and OFFSET <n> to the query
example SELECT * FROM users LIMIT 1 OFFSET 5132;

Answer 2

The general approach is that, if you want only the nth row of m rows, use an appropriate where condition to only get that row.