How to read every nth row in Qt Sqlite?
-
Hello All, I'm a newbie in Qt and am using Qt Sqlite as the database for my Qt application. While querying the database, I need to get the values between time x and y and I need to get only every nth value between this range, starting from x. Is there a way I can do this? Alternately, while reading from the database, I can skip n-1 values and read the nth value, but is there a way I can do this directly from the database?
Currently, I have the query like ("SELECT * FROM TableName WHERE rowid BETWEEN x AND y)
rowid is the unique id I have given for every row.
How can I expand this query to exclude n-1 values and get every nth value? MOD may not be an option as I require the starting row x, even if it does not satisfy the condition MOD n == 0 -
I have no idea of SqLite allows you to use mod as an operator in SQL. If it does, it is easy to do:
@
SELECT * FROM Table WHERE (rowid BETWEEN x AND y) AND (mod ( rowid - x, interval) = 0 )
@
That is, substract the x from the rowid before doing the modulo with the interval, and you have assured yourself that row x is always part of the result. -However, I doubt the operator or function exists in sqlite.- I would simply do it in code then. Just like you can skip reading the rows where your rowid < x, you can also skip reading rows between your interval.Edit: Thanks for correcting me.
-
SQLite supports the modulo operator (%) and the query should be possible as suggested by Andre using a "normalized" row id.
@
SELECT *
FROM Table
WHERE (time between X and Y)
AND (rowid - (SELECT rowid
FROM Table
WHERE time > X
ORDER BY time
LIMIT 1)) % N = 0;
@This query should return every Nth row between time X and Y.
SQLite automatically creates unique ids for every row (accessible through rowid, _ rowid _ or oid) so there is no need to create an explicit column for row ids.
Be careful that this is pure SQLite and possibly not portable to any other SQL dialect.
So the better solution, or the more portable solution, would be strict SQL and programmatical selection of the rows as Andre suggested. If you strive for perfomance you should test both variants.