19

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

GileBrt
  • 1,830
  • 3
  • 20
  • 28
David Homes
  • 2,725
  • 8
  • 33
  • 53

3 Answers3

35

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

wlk
  • 5,695
  • 6
  • 54
  • 72
  • sounds great but i don't know if this works on sqlite3, will try it out tough – David Homes Aug 05 '10 at 22:18
  • Is it a zero based index? – Kamran Ahmed Aug 30 '14 at 14:43
  • It is like it is zero indexed into what the query would be if you didn't use limit. If you want to start at the 3rd row then the offset is 2: "SELECT * FROM tableName LIMIT 3 OFFSET 2" ... this can also be written in reverse order using slightly more compact syntax as in this stack answer: http://stackoverflow.com/questions/3325515/sqlite-limit-offset-query – WaterNotWords Aug 06 '15 at 04:16
1

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

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.

If you need to get to a row and can't because no where criteria can get you there, your database has a serious design issue. It fails the first normal form, which states that "There's no top-to-bottom ordering to the rows."

MPelletier
  • 16,256
  • 15
  • 86
  • 137