Thank you for all suggestions.
I will need to do such queries often, so it's just a matter of saving
the typing. Unfortunately, views aren't going to be very practical
either, because there are a lot of tables and columns (100+), and new
ones will be added.
The actual use case is as follows:
I have tables with a timestamp (unix time) and columns containing
sensor readings which are inserted continuously. I frequently need to
access the most recent values (or NULL if there is no value within the
latest, say, hour). I would like to do something like:
SELECT coalesce(col_1), ..., coalesce(col_n) FROM v WHERE unix_time >
strftime('%s', 'now', '-1 hour') ORDER BY unix_time DESC;
So I would typically want to access the last non-NULL value because of
the DESC keyword. But if I understand things correctly, a statement
like above will never work because an aggregate function reads the
data in no particular order regardless of the ORDER BY statement.
I like Igor's suggestion. Although not quite universal, it's clever.
Ryan's suggestion should work well, except that I will need a first(a,
b) and last(a, b) function (if I want to support both ascending and
descending order) and I can leave out the ORDER BY part. So:
SELECT last(col_1, unix_time), ..., last(col_n, unix_time) FROM v WHERE
unix_time > strftime('%s', 'now', '-1 hour');
Yes, it will have run through the whole set, whereas multiple SELECT
col_x FROM v WHERE unix_time > strftime('%s', 'now', '-1 hour') AND
col_x IS NOT NULL ORDER BY unix_time DESC LIMIT 1 will stop early.
But this will not be a problem for me since I want to have a modest
upper limit (1 hour) anyway.
--
Steinar Midtskogen
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
