Re: [sqlite] Find first non-NULL values of several columns

How's about you store your interpolated value during insert? You can use a binary mask of say, 16384, to indicate the value is interpolated in case you need to know that. In the original data you sent only one value can be interpolated at record 3. You probably want the interpolation to be we

Re: [sqlite] Find first non-NULL values of several columns

[Simon Slavin] > On 20 Oct 2012, at 3:57am, Keith Medcalf wrote: > >> While this is presently how SQLite works, it may not always be so. It is >> possible that a future version may change the order of traversal. > > Or indeed > >

Re: [sqlite] Find first non-NULL values of several columns

On Fri, Oct 19, 2012 at 10:57 PM, Keith Medcalf wrote: > > As far as I can tell, the SQLite engine does an inorder traversal of the > tree when doing a table scan (ie, from lowest to highest) returning rows in > rowid order. > While this is presently how SQLite works, it may not always be so. >

Re: [sqlite] Find first non-NULL values of several columns

On 20 Oct 2012, at 3:57am, Keith Medcalf wrote: > While this is presently how SQLite works, it may not always be so. It is > possible that a future version may change the order of traversal. Or indeed Simon. __

Re: [sqlite] Find first non-NULL values of several columns

oun...@sqlite.org] On Behalf Of Steinar Midtskogen > Sent: Friday, 19 October, 2012 15:43 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Find first non-NULL values of several columns > > "Keith Medcalf" writes: > > > Alternatively, to implemen

Re: [sqlite] Find first non-NULL values of several columns

"Keith Medcalf" writes: > Alternatively, to implement your original question, write an aggregate > function which returns the first non-null value it comes across, and use the > negated unix timestamp as an explicit rowid, depending on the fact that a > table-scan does an in-order traversal of

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson writes: > On 19/10/2012 3:09 PM, Steinar Midtskogen wrote: >> 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 >> lates

Re: [sqlite] Find first non-NULL values of several columns

On 19 Oct 2012, at 8:09pm, Steinar Midtskogen wrote: > 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 woul

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson wrote: > Is each column associated with a different sensor? If so, do sensors > routinely generate data at the same time and combine their entries? Do > sensors emit data frequently? > > If any of the above is false, I'd definitely store each column in its > own table Or else, every

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 3:09 PM, Steinar Midtskogen wrote: 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 wil

Re: [sqlite] Find first non-NULL values of several columns

create table taglist (tagid integer primary key autoincrement, tagname text collate nocase unique); create table tagdata (tagid integer references taglist(tagid), timestamp integer not null, value not null, unique(tagid, timestamp)); select tagname, coalesce(timestamp, tsstart), value from ta

Re: [sqlite] Find first non-NULL values of several columns

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

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 9:17 AM, Igor Tandetnik wrote: Ryan Johnson wrote: I'd go for a user-defined aggregate taking two args: the key (to identify "first") and the value to coalesce. Sure, it would never stop the scan early, but the benefit of doing one scan instead of five probability outweighs that (

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson wrote: > I'd go for a user-defined aggregate taking two args: the key (to > identify "first") and the value to coalesce. Sure, it would never stop > the scan early, but the benefit of doing one scan instead of five > probability outweighs that (unless Steinar has an appropriate index

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 8:55 AM, Igor Tandetnik wrote: Steinar Midtskogen wrote: Ok, so let's say the table v (with "a" as the primary key) is: a|b|c|d|e|f 0| | |2| |9 1|1| |3| |8 2|1| |4|4|7 3| |5|5|4|6 4|1|6|6| |5 The the question becomes, is there a more convenient way to do: SELECT * FROM (SELECT

Re: [sqlite] Find first non-NULL values of several columns

Igor Tandetnik wrote: > If the values of b, c and so on have a known upper bound, then you can write > something like > > select min(a*1000 + b), min(a*1000 + c), ..., min(a*1000 + f) from v; I mean, select min(a*1000 + b) % 1000, ... or the same with shifts and masks: select min(a<<32 + b)

Re: [sqlite] Find first non-NULL values of several columns

Steinar Midtskogen wrote: > Ok, so let's say the table v (with "a" as the primary key) is: > > a|b|c|d|e|f > 0| | |2| |9 > 1|1| |3| |8 > 2|1| |4|4|7 > 3| |5|5|4|6 > 4|1|6|6| |5 > > The the question becomes, is there a more convenient way to do: > > SELECT * FROM (SELECT b FROM v WHERE b IS NOT

Re: [sqlite] Find first non-NULL values of several columns

: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Find first non-NULL values of several columns Simon Slavin writes: > Rows do not have an order. Without an ORDER BY clause SELECT can return rows > in a random order if it wants. If you would like to define 'order'

Re: [sqlite] Find first non-NULL values of several columns

Simon Slavin writes: > Rows do not have an order. Without an ORDER BY clause SELECT can return rows > in a random order if it wants. If you would like to define 'order' for me I > can give you a SELECT which will find the first non-NULL value in a column, > probably something like > > SELECT

Re: [sqlite] Find first non-NULL values of several columns

On 19 Oct 2012, at 12:06pm, Steinar Midtskogen wrote: > Suppose I have this table v: > > a|b|c|d|e > | |2| |9 > 1| |3| |8 > 1| |4|4|7 > |5|5|4|6 > 1|6|6| |5 > > And I would like to return the first non-NULL value of each column. Rows do not have an order. Without an ORDER BY clause SELECT ca

[sqlite] Find first non-NULL values of several columns

Suppose I have this table v: a|b|c|d|e | |2| |9 1| |3| |8 1| |4|4|7 |5|5|4|6 1|6|6| |5 And I would like to return the first non-NULL value of each column. I can do somthing like: SELECT * FROM (SELECT a FROM v WHERE a IS NOT NULL LIMIT 1), (SELECT b FROM v WHERE b IS NOT NULL LI