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

user_98907 0
Viewed 11728

Quicky question on SQLite3 (may as well be general SQLite)

How can one retrieve the n-th 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 very much because of performance issues.
So, if anyone can drop a hint that'd be highly appreciated.

Regards david

sqlite mysql sql
3 Answers

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

In scenario when you need non-continuous rows you could use ROW_NUMBER():

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER() AS rn --OVER(ORDER BY ...) --if specific order is required
  FROM t
)
SELECT c 
FROM cte
WHERE rn IN (2,5);  -- row nums

db<>fiddle demo

user_50708790
Related