1

What's the best SQL query in SQLite to get the Nth item from a list of data.

The data does not have numeric keys

Ian Vink
  • 66,960
  • 104
  • 341
  • 555
  • Possible duplicate of [SQLite3 (or general SQL) retrieve nth row of a query result](http://stackoverflow.com/questions/3419626/sqlite3-or-general-sql-retrieve-nth-row-of-a-query-result) – Ciro Santilli OurBigBook.com Dec 06 '15 at 10:39

3 Answers3

10

You want OFFSET. 

SELECT mycol FROM mytable ORDER BY mycol LIMIT 1 OFFSET 11;

Shorthand version:

SELECT mycol FROM mytable ORDER BY mycol LIMIT 11,1;

Link to documentation which describes OFFSET as follows:

The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set.

mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
3

So, say your query was 

SELECT * from myTable
ORDER BY someField

You could use LIMIT and OFFSET to

SELECT * from myTable
ORDER BY someField
LIMIT 1 OFFSET 11

I'm looking at this documentation to get that. I think that limits you to one result, skipping the first 11 rows.

artlung
  • 33,305
  • 16
  • 69
  • 121
1

Use this if you don't know any field types you can sort by, then loop to the last record:

select * from table limit 12

If there is a field that will put the table in order, this should get you the 12th record:

select * from table where field = (select field from table order by field desc limit 12) limit 1

EDIT: This is SqLite 2.x syntax before OFFSET was introduced. (The last version I used.)

Sophtware
  • 1,796
  • 2
  • 21
  • 34