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 o
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 return
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 l
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 and
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 t
> 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 chan
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 wo
ffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Jose Isaias Cabrera
>Sent: Tuesday, 14 January, 2020 06:19
>To: SQLite mailing list
>Subject: Re: [sqlite] Capturing the changes in columns in a table
>
>
>
>Keith Medcalf, on Monday, January 1
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, entri
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 cha
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", 'c'
rway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Monday, 13 January, 2020 17:04
>To: SQLite mailing list
>Subject: Re: [sqlite] Capturing the changes in columns in a table
>
>
>
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 e
Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Monday, 13 January, 2020 16:51
>To: SQLite mailing list
>Subject: Re: [sqlite] Capturing the changes in columns in a table
>
>
&
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,
'2
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 th
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
p002|2|4|
20 matches
Mail list logo
