sqLite OFFSET query does not work with WHERE clause?

Question

Can someone explain why this sqlite3 query will work and return my 1000 records:

SELECT * FROM fmsdata LIMIT 1000 OFFSET (SELECT COUNT(*) FROM fmsdata) - 1000

but the minute I drop in the where clause WHERE valve=3 it returns nothing?

SELECT * FROM fmsdata WHERE valve=3 LIMIT 1000 OFFSET (SELECT COUNT(*) FROM fmsdata) - 1000

This sub query returns the table size which is 123290.

SELECT COUNT(*) FROM fmsdata

FYI the offset is just to quickly give me the tail end of the database without having to sort anything since I know the latest records will always be at the end.

Here is a successful query without the where clause:

Our test table has records looping roughly around 102 valves so with a limit of 1000 we should have at least 9 entries that showed up at the tail end of the query.

Not sure if it matters but I am on a Ubuntu 18.04 system.

Answer 1

This query:

SELECT * FROM fmsdata WHERE valve=3 LIMIT 1000 OFFSET (SELECT COUNT(*) FROM fmsdata) - 1000

according to your data, is equivalent to:

SELECT * FROM fmsdata WHERE valve=3 LIMIT 1000 OFFSET 123290 - 1000

or

SELECT * FROM fmsdata WHERE valve=3 LIMIT 1000 OFFSET 122290

and this is translated to:

From the rows where valve=3, skip the first 122290 and then show me maximum 1000 rows.

Do you have more than 122290 rows where valve=3?
I don't think so. This is why you get nothing.
I think what you really want is this:

SELECT * FROM fmsdata WHERE valve=3 
LIMIT 1000 
OFFSET (SELECT COUNT(*) FROM fmsdata WHERE valve=3) - 1000

This means if you have 1100 rows where valve=3, the first 100 (= 1100 - 1000) will be skipped and the remaining 1000 will be selected.
If you have 1000 or less rows where valve=3 then all these rows will be selected.
As a side note:
when you use LIMIT without ORDER BY you must be aware that the result is not guaranteed to be accurate.