31

I am trying to query a sql lite database with only an offset and no limit.

SELECT [Id], [Name], [IntValue], [IntNulableValue] FROM [Product] OFFSET 10

I can do an offset query when I have a limit however (LIMIT 10 OFFSET 10).

Here is the error sql lite is giving me.

SQLite error near "10": syntax error
Cœur
  • 37,241
  • 25
  • 195
  • 267
Paul Knopf
  • 9,568
  • 23
  • 77
  • 142

2 Answers2

70

Just set LIMIT to -1.

For example:

SELECT * FROM table LIMIT -1 OFFSET 10
Ming-Hong Bai
  • 815
  • 7
  • 5
  • It would be great if Mysql would implement the same idea – elipoultorak Oct 26 '15 at 10:59
  • 2
    I guess -1 is the more often seen than 2^64-1, and shorter, but in terms of overall cleanliness they're both magical numbers. The clean solution would be to separate limit and offset given they serve separate use cases. Chocolate and milk go great together, but sometimes I want one without the other! – aaaaaa May 22 '16 at 20:38
12

On the SQL as understood by SQLite page, you'll notice that OFFSET isn't understood without LIMIT.

http://sqlite.org/lang_select.html

According to the same documentation:

If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned.

Mark Paine
  • 1,874
  • 13
  • 14
  • Looks like I will have to make the limit long.MaxValue, thanks! – Paul Knopf May 08 '12 at 04:17
  • Good thing to add. Other Databases allow to use the OFFSET clause after the LIMIT clause. In SQLite you are required to use LIMIT before the OFFSET. – ahwelp Mar 24 '22 at 13:31