sqlitepython-sql

Count with limit and offset in sqlite


am am trying to write a function in python to use sqlite and while I managed to get it to work there is a behavior in sqlite that I dont understand when using the count command. when I run the following sqlite counts as expected, ie returns an int.

SELECT COUNT (*) FROM Material WHERE level IN (?) LIMIT 10

however when I add, shown below, an offset to the end sqlite returns an emply list, in other words nothing.

SELECT COUNT (*) FROM Material WHERE level IN (?) LIMIT 10 OFFSET 82

while omitting the offset is an easy fix I don't understand why sqlite returns nothing. Is this the expected behavior for the command I gave?

thanks for reading


Solution

  • When you execute that COUNT(*) it will return you only a single row.
    The LIMIT function is for limiting the number of rows returned. You are setting the limit to 10 which doesn't have any effect here (Because it is returning only a single row).
    OFFSET is for offsetting/skipping specified number of rows. Which also doesn't have any effect here.

    In simple terms your query translates to COUNT number of rows, then return 10 rows starting from 83rd position. Since you've a single row it will always return empty.

    Read about LIMIT and OFFSET