SQLite get the 12th record - most efficient?

Question

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

Answer 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.)

Answer 2

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.

Answer 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.