0

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:

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

forpas
  • 160,666
  • 10
  • 38
  • 76
simgineer
  • 1,754
  • 2
  • 22
  • 49

1 Answers1

3

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Excellent answer. I would emphasis on your last sentence. OP statement "_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._" is really where it all goes wrong. @simgineer You should never assume that this `LIMIT` statement without `ORDER BY DESC`, will bring back the last records inserted in the table – Thomas G Mar 02 '19 at 11:54
  • *You should never assume...* this is the point. Anything not documented, even if it seems *obvious* can go wrong. – forpas Mar 02 '19 at 12:00
  • Adding the WHERE clause in the sub query fixes everything. I was avoiding the ORDER BY to speed up the query since we knew the records would always be inserted in chronological order for these particular files. I suppose the advise is that once we start using any non trivial operation the actual order is no longer guaranteed. Thank you forpas for this answer. – simgineer Mar 03 '19 at 00:22