David Raymond wrote:
> My brain started yelling that that needed a "limit 1" on the subquery so that
> it would only return 1 row.
>
> How is that handled by other databases?
SQL-92 says:
| 6.11
|
| General Rules
|
| 2) If a is a and the
| result of the is empty, then the result
On 2020/01/14 7:10 PM, David Raymond wrote:
A note and a question on subqueries. On reading:
select
...
(select b from t where a == new.a and idate < new.idate order by idate desc) as
oldv,
...
My brain started yelling that that needed a "limit 1" on the subquery so that
it would only
Keith Medcalf, on Tuesday, January 14, 2020 11:49 AM, wrote...
>
>
> On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera
> wrote:
>
> >That is an idea I have not thought about, Neal. Thanks. The boss comes
> >up with lots of requests, and these have to be done yesterday. I will
> >have to
A note and a question on subqueries. On reading:
select
...
(select b from t where a == new.a and idate < new.idate order by idate desc) as
oldv,
...
My brain started yelling that that needed a "limit 1" on the subquery so that
it would only return 1 row.
I looked in the SQLite docs though
On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera
wrote:
>That is an idea I have not thought about, Neal. Thanks. The boss comes
>up with lots of requests, and these have to be done yesterday. I will
>have to look into triggers. Have not used them yet. :-(
Here is some sample triggers
sub sk79, on Tuesday, January 14, 2020 10:54 AM, wrote...
>
> > Yes, a dumb of a system is provided daily
>
>
> There are some great solutions already here for offline processing as
> stated in your question. However, something you might also want to
> consider
> (if you have requisite access to
> Yes, a dumb of a system is provided daily
There are some great solutions already here for offline processing as
stated in your question. However, something you might also want to consider
(if you have requisite access to the system) is to use ‘after insert’
trigger(s) to online capture the
Keith Medcalf, on Tuesday, January 14, 2020 09:04 AM, wrote...
>
>
> Assuming (a, idate) is indexed and unique, then give the following a
> whirl on your larger data. It does the same thing but does not use window
> functions to find the prior value -- it does a correlated subquery instead.
> I
Assuming (a, idate) is indexed and unique, then give the following a whirl on
your larger data. It does the same thing but does not use window functions to
find the prior value -- it does a correlated subquery instead. I would expect
that it is slower with real data than the window function
Jean-Luc Hainaut, on Tuesday, January 14, 2020 07:25 AM, wrote...
>
>
> Another version that doesn't use CTE nor window functions:
>
> select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
> as newVal
> from t t1,t t2
> where t1.a = t2.a
> andt2.idate = date(t1.idate,'+1
Keith Medcalf, on Monday, January 13, 2020 08:03 PM, wrote...
>
>
> And this version is several times faster since only the changes are
> union'd which minimizes the total number of records processed.
> The index should be "create index i on t (a, idate);" Because of the way
> indexes work,
R Smith, on Monday, January 13, 2020 06:49 PM, wrote...
>
>
> On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote:
> > R Smith, on Monday, January 13, 2020 05:25 PM, wrote...
> >>
> >
> > Wow! Thanks for this. I had not thought about your questions. My boss
> said, I need to know all the
Another version that doesn't use CTE nor window functions:
select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
as newVal
from t t1,t t2
where t1.a = t2.a
andt2.idate = date(t1.idate,'+1 day')
andt1.b <> t2.b
union all
select t1.a as Proj, t2.idate as "On",
And this version is several times faster since only the changes are union'd
which minimizes the total number of records processed.
The index should be "create index i on t (a, idate);" Because of the way
indexes work, entries on the same a, idate will be ordered by n. (though really
idate
Jose, I like Keith's version better using the Windowing functions
assuming your version of SQLite is newer than 3.27 (or whenever Window
functions were introduced, again my memory fails...)
Most importantly, the CTE query /requires/ changes be day-on-day to be
seen, which is the case in your
Note this only requires that "idate" be a unique orderable sequence within "a"
in order to work. It does not have to be particular (such as a date/datetime).
It can be a date, a datetime, an integer (as in unixtime), a real (as in
julianday number), or any old sequence number and it will still
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote:
R Smith, on Monday, January 13, 2020 05:25 PM, wrote...
Wow! Thanks for this. I had not thought about your questions. My boss said,
I need to know all the changes per project whenever it happened. So,... I will
have to revise my
R Smith, on Monday, January 13, 2020 05:25 PM, wrote...
> On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:
> > Greetings!
> >
> > Please observe the following,
> >
> >
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> >
> > insert into t (a, b, c, d, e, idate) values
On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:
Greetings!
Please observe the following,
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
'2019-02-11');
...
p001|1|10|column b changed on 2019-02-12
Greetings!
Please observe the following,
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
'2019-02-11');
insert into t (a, b,
21 matches
Mail list logo
