Re: [sqlite] Grouping and grabbing one item
no...@null.net wrote: > On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote: >> select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), >> max(ed),target, sum(amt) from Tasks where Pid=1 group by target >> HAVING amt > 0; > > When I first read that query I wondered if putting a query inside a > select expression would execute it for each row. Only correlated subqueries are executed for each row. All other subqueries are executed only once (when they are first needed). Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
Since you are eventually going to come to the part where you need to include the quote for the correct target, lets include that too, just to skip ahead: select (SELECT ed from Tasks as I where I.task = 'QUOTE' and I.Pid = O.Pid and I.target like '%' || O.Target || '%' ), max(O.ed), O.target, sum(O.amt) from Tasks as O where ... group by target having sum(amt) > 0; NB: The data is not relational. You should make it so and not overload multiple entries into a single value (ie, make sure your data is at in at least first normal form). Then you can replace the "like" with a simple equals. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of jose isaias cabrera >Sent: Wednesday, 18 October, 2017 16:39 >To: SQLite mailing list >Subject: Re: [sqlite] Grouping and grabbing one item > > >Ok, I missed a condition. Imagine this set of data, > > >CREATE TABLE Tasks ( > id INTEGER PRIMARY KEY, > Pid INTEGER, > cust TEXT, > period TEXT, > bd TEXT, > ed TEXT, > task TEXT, > target TEXT, > amt REAL >); > >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-28','2017-10-01','DOC','es-ES',100); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-28','2017-10-01','DOC','it-IT',120); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-28','2017-10-01','DOC','fr-FR',110); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-28','2017-10-01','VAL','es-ES',70); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-28','2017-10-01','VAL','fr-FR',75); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(1,'A','aa','2017-09-28','2017-10-01','VAL','it-IT',80); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-28','2017-10-01','DOC','es-ES',100); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-28','2017-10-01','DOC','it-IT',120); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-28','2017-10-01','DOC','fr-FR',110); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-28','2017-10-01','VAL','es-ES',70); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-28','2017-10-01','VAL','fr-FR',75); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(2,'Z','aa','2017-09-28','2017-10-01','VAL','it-IT',80); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-10-03','2017-10-04','QUOTE','es-ES fr-FR it-IT',0); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-09-28','2017-10-01','DOC','es-ES',200); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-09-28','2017-10-01','DOC','it-IT',320); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-09-28','2017-10-01','DOC','fr-FR',410); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-09-28','2017-10-01','VAL','es-ES',170); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-09-28','2017-10-01','VAL','fr-FR',275); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(3,'A','aa','2017-09-28','2017-10-01','VAL','it-IT',180); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-28','2017-10-01','DOC','es-ES',100); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-28','2017-10-01','DOC','it-IT',120); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-28','2017-10-01','DOC','fr-FR',110); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-28','2017-10-01','VAL','es-ES',70); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-28','2017-10-01','VAL','fr-FR',75); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(4,'F','aa','2017-09-28','2017-10-01','VAL','it-IT',80); >INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES >(5,'F','ab','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); >INSERT I
Re: [sqlite] Grouping and grabbing one item
Ok, I missed a condition. Imagine this set of data, CREATE TABLE Tasks ( id INTEGER PRIMARY KEY, Pid INTEGER, cust TEXT, period TEXT, bd TEXT, ed TEXT, task TEXT, target TEXT, amt REAL ); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (1,'A','aa','2017-09-28','2017-10-01','VAL','it-IT',80); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (2,'Z','aa','2017-09-28','2017-10-01','VAL','it-IT',80); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-10-03','2017-10-04','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-09-28','2017-10-01','DOC','es-ES',200); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-09-28','2017-10-01','DOC','it-IT',320); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-09-28','2017-10-01','DOC','fr-FR',410); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-09-28','2017-10-01','VAL','es-ES',170); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-09-28','2017-10-01','VAL','fr-FR',275); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (3,'A','aa','2017-09-28','2017-10-01','VAL','it-IT',180); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (4,'F','aa','2017-09-28','2017-10-01','VAL','it-IT',80); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,cust,period,bd,ed,task,target,amt) VALUES (5,'F','ab','2017-09-28','2017-10-01','VAL','it-IT',80); I know I can do, select max(ed),target, sum(amt) from Tasks where cust='A' group by Pid, target HAVING amt > 0; to get, 2017-10-01|es-ES|170.0 2017-10-01|fr-FR|185.0 2017-10-01|it-IT|200.0 2017-10-01|es-ES|370.0 2017-10-01|fr-FR|685.0 2017-10-01|it-IT|500.0 but I want the QUOTE ed for the correct Pid in front again. I have no idea how to do this. I was trying some JOINs, select ls.ed,max(ls.
Re: [sqlite] Grouping and grabbing one item
This last one does appear faster... Thanks. -Original Message- From: no...@null.net Sent: Wednesday, October 18, 2017 3:17 PM To: SQLite mailing list Subject: Re: [sqlite] Grouping and grabbing one item On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote: select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; When I first read that query I wondered if putting a query inside a select expression would execute it for each row. I don't know if that is the case, but here is the query plan: explain query plan select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; 0,0,0,"SCAN TABLE Tasks" 0,0,0,"USE TEMP B-TREE FOR GROUP BY" 0,0,0,"EXECUTE SCALAR SUBQUERY 1" 1,0,0,"SCAN TABLE Tasks" It would appear that moving the subquery down into a FROM clause makes the query plan look slightly better. In my humble opinion it also makes the query easier to understand. explain query plan select q.ed, max(tasks.ed), target, sum(amt) from (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1) q inner join Tasks on Pid=1 group by q.ed, target HAVING amt > 0; 0,0,0,"SCAN TABLE Tasks" 0,1,1,"SEARCH TABLE Tasks USING AUTOMATIC PARTIAL COVERING INDEX (Pid=?)" 0,0,0,"USE TEMP B-TREE FOR GROUP BY" The above is the case with version 3.16.2. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote: > select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), > max(ed),target, sum(amt) from Tasks where Pid=1 group by target > HAVING amt > 0; When I first read that query I wondered if putting a query inside a select expression would execute it for each row. I don't know if that is the case, but here is the query plan: explain query plan select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; 0,0,0,"SCAN TABLE Tasks" 0,0,0,"USE TEMP B-TREE FOR GROUP BY" 0,0,0,"EXECUTE SCALAR SUBQUERY 1" 1,0,0,"SCAN TABLE Tasks" It would appear that moving the subquery down into a FROM clause makes the query plan look slightly better. In my humble opinion it also makes the query easier to understand. explain query plan select q.ed, max(tasks.ed), target, sum(amt) from (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1) q inner join Tasks on Pid=1 group by q.ed, target HAVING amt > 0; 0,0,0,"SCAN TABLE Tasks" 0,1,1,"SEARCH TABLE Tasks USING AUTOMATIC PARTIAL COVERING INDEX (Pid=?)" 0,0,0,"USE TEMP B-TREE FOR GROUP BY" The above is the case with version 3.16.2. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
Thanks, I didn't know that was possible. -Original Message- From: Paul Sanderson Sent: Wednesday, October 18, 2017 2:10 PM To: SQLite mailing list Subject: Re: [sqlite] Grouping and grabbing one item How about select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt 0; 2017-09-27|2017-10-01|es-ES|170.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 18 October 2017 at 18:23, jose isaias cabrera wrote: CREATE TABLE Tasks ( id INTEGER PRIMARY KEY, Pid INTEGER, bd TEXT, ed TEXT, task TEXT, target TEXT, amt REAL ); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','it-IT',80); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','it-IT',80); I know I can do, select max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; and get, 2017-10-01|es-ES|100.0 2017-10-01|fr-FR|185.0 2017-10-01|it-IT|200.0 but, I would like to add the ed of the task='QUOTE' to the beginning of the list. So, the result would look like this, 2017-09-27|2017-10-01|es-ES|100.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 I know how to select it by itself, SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; but I need to add it to the beginning of the list with a JOIN or something. Any thoughts? Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
ahh bugger - google didn't show the new answers had popped up. Pleased I came up with a working solution though :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 18 October 2017 at 19:03, jose isaias cabrera wrote: > > So simple! Thanks, Darko. > > -Original Message- From: Darko Volaric > Sent: Wednesday, October 18, 2017 1:57 PM > To: SQLite mailing list > Subject: Re: [sqlite] Grouping and grabbing one item > > > select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), > max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > > 0; > > > On Oct 18, 2017, at 7:23 PM, jose isaias cabrera >> wrote: >> >> >> CREATE TABLE Tasks ( >> id INTEGER PRIMARY KEY, >> Pid INTEGER, >> bd TEXT, >> ed TEXT, >> task TEXT, >> target TEXT, >> amt REAL >> ); >> >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-28','2017-10-01','DOC','es-ES',100); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-28','2017-10-01','DOC','it-IT',120); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-28','2017-10-01','VAL','es-ES',70); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (1,'2017-09-28','2017-10-01','VAL','it-IT',80); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-28','2017-10-01','DOC','es-ES',100); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-28','2017-10-01','DOC','it-IT',120); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-28','2017-10-01','VAL','es-ES',70); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); >> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES >> (2,'2017-09-28','2017-10-01','VAL','it-IT',80); >> >> I know I can do, >> >> select max(ed),target, sum(amt) from Tasks where Pid=1 group by target >> HAVING amt > 0; >> >> and get, >> >> 2017-10-01|es-ES|100.0 >> 2017-10-01|fr-FR|185.0 >> 2017-10-01|it-IT|200.0 >> >> but, I would like to add the ed of the task='QUOTE' to the beginning of >> the list. So, the result would look like this, >> >> 2017-09-27|2017-10-01|es-ES|100.0 >> 2017-09-27|2017-10-01|fr-FR|185.0 >> 2017-09-27|2017-10-01|it-IT|200.0 >> >> I know how to select it by itself, >> >> SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; >> >> but I need to add it to the beginning of the list with a JOIN or >> something. Any thoughts? Thanks. >> >> josé >> > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
How about select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; 2017-09-27|2017-10-01|es-ES|170.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 18 October 2017 at 18:23, jose isaias cabrera wrote: > > CREATE TABLE Tasks ( > id INTEGER PRIMARY KEY, > Pid INTEGER, > bd TEXT, > ed TEXT, > task TEXT, > target TEXT, > amt REAL > ); > > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','es-ES',100); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','it-IT',120); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','es-ES',70); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','it-IT',80); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','es-ES',100); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','it-IT',120); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','es-ES',70); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','it-IT',80); > > I know I can do, > > select max(ed),target, sum(amt) from Tasks where Pid=1 group by target > HAVING amt > 0; > > and get, > > 2017-10-01|es-ES|100.0 > 2017-10-01|fr-FR|185.0 > 2017-10-01|it-IT|200.0 > > but, I would like to add the ed of the task='QUOTE' to the beginning of > the list. So, the result would look like this, > > 2017-09-27|2017-10-01|es-ES|100.0 > 2017-09-27|2017-10-01|fr-FR|185.0 > 2017-09-27|2017-10-01|it-IT|200.0 > > I know how to select it by itself, > > SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; > > but I need to add it to the beginning of the list with a JOIN or > something. Any thoughts? Thanks. > > josé > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
So simple! Thanks, Darko. -Original Message- From: Darko Volaric Sent: Wednesday, October 18, 2017 1:57 PM To: SQLite mailing list Subject: Re: [sqlite] Grouping and grabbing one item select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; On Oct 18, 2017, at 7:23 PM, jose isaias cabrera wrote: CREATE TABLE Tasks ( id INTEGER PRIMARY KEY, Pid INTEGER, bd TEXT, ed TEXT, task TEXT, target TEXT, amt REAL ); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','it-IT',80); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','it-IT',80); I know I can do, select max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; and get, 2017-10-01|es-ES|100.0 2017-10-01|fr-FR|185.0 2017-10-01|it-IT|200.0 but, I would like to add the ed of the task='QUOTE' to the beginning of the list. So, the result would look like this, 2017-09-27|2017-10-01|es-ES|100.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 I know how to select it by itself, SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; but I need to add it to the beginning of the list with a JOIN or something. Any thoughts? Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping and grabbing one item
select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; > On Oct 18, 2017, at 7:23 PM, jose isaias cabrera > wrote: > > > CREATE TABLE Tasks ( > id INTEGER PRIMARY KEY, > Pid INTEGER, > bd TEXT, > ed TEXT, > task TEXT, > target TEXT, > amt REAL > ); > > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','es-ES',100); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','it-IT',120); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','es-ES',70); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (1,'2017-09-28','2017-10-01','VAL','it-IT',80); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','es-ES',100); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','it-IT',120); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','es-ES',70); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); > INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES > (2,'2017-09-28','2017-10-01','VAL','it-IT',80); > > I know I can do, > > select max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING > amt > 0; > > and get, > > 2017-10-01|es-ES|100.0 > 2017-10-01|fr-FR|185.0 > 2017-10-01|it-IT|200.0 > > but, I would like to add the ed of the task='QUOTE' to the beginning of the > list. So, the result would look like this, > > 2017-09-27|2017-10-01|es-ES|100.0 > 2017-09-27|2017-10-01|fr-FR|185.0 > 2017-09-27|2017-10-01|it-IT|200.0 > > I know how to select it by itself, > > SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; > > but I need to add it to the beginning of the list with a JOIN or something. > Any thoughts? Thanks. > > josé > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Grouping and grabbing one item
CREATE TABLE Tasks ( id INTEGER PRIMARY KEY, Pid INTEGER, bd TEXT, ed TEXT, task TEXT, target TEXT, amt REAL ); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (1,'2017-09-28','2017-10-01','VAL','it-IT',80); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','es-ES',100); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','it-IT',120); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','DOC','fr-FR',110); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','es-ES',70); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','fr-FR',75); INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES (2,'2017-09-28','2017-10-01','VAL','it-IT',80); I know I can do, select max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; and get, 2017-10-01|es-ES|100.0 2017-10-01|fr-FR|185.0 2017-10-01|it-IT|200.0 but, I would like to add the ed of the task='QUOTE' to the beginning of the list. So, the result would look like this, 2017-09-27|2017-10-01|es-ES|100.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 I know how to select it by itself, SELECT ed from Tasks where task = 'QUOTE' and Pid = 1; but I need to add it to the beginning of the list with a JOIN or something. Any thoughts? Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems compiling FTS5 extension
On 10/18/2017 06:32 PM, Eugene Mirotin wrote: In short the error I get is fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named '__builtin___snprintf_chk' More details in SO question here: https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension, please let me know if I should paste everything in my email. I've seen a similar problem reported before and somehow related to XCode, but that issue was reported to be fixed. Would be thankful for any tips, I haven't used C for years and have no idea where to start. Please try with the latest trunk checkin: http://www.sqlite.org/src/info/cd0471ca9f75e7c8 (click the "ZIP archive" link to download if you're not using fossil) To generate the sqlite3ext.h and sqlite3.h files required when compiling fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something like: ./configure make fts5.c sqlite3.h sqlite3ext.h gcc -O2 -fPIC -shared fts5.c -o fts5.dylib Or, if you want to use a released version, after [make sqlite3ext.h] replace the two instances of "snprintf" in the sqlite3ext.h with "xsnprintf". Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] materializing views over virtual tables
Still using SQLite 3.7.14: I have two identically declared virtual tables that differ only in the backing store (memory section vs. CTree files), and a view. CREATE VIRTUAL TABLE pools_MM using Memory(); CREATE VIRTUAL TABLE pools_CT using CTree(); CREATE VIEW pools_VW AS SELECT * FROM pools_MM UNION ALL SELECT * FROM pools_CT; Both tables support an index with 9 fields (a, b, c, d, e, f, g, h, i). There are also fields corresponding to monetary values (pi, ca, rb, crb). When running the following statement (note that key field d is not used) SELECT SUM(pi - ca - rb + crb) FROM pools_VW where a=7 and b=9 and c=2448 and e=1 and g=2 and f IN (1,2,4,5); SQLite is materializing the view via full table scans, which makes it run very slowly. The query plan looks like this sele order from deta - 2 0 0 SCAN TABLE pools_MM VIRTUAL TABLE INDEX 0: (~0 rows) 3 0 0 SCAN TABLE pools_CT VIRTUAL TABLE INDEX 0: (~0 rows) 1 0 0 COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) 0 0 0 SCAN SUBQUERY 1 (~1 rows) 0 0 0 EXECUTE LIST SUBQUERY 4 Or, expressed as a sequence of SELECTS: CREATE TEMP TABLE T1 AS SELECT * FROM pools_MM; INSERT INTO T1 SELECT * FROM pools_CT; CREATE TEMPT TABLE T2 (f integer); INSERT INTO T2 VALUES (1),(2),(4),(5); SELECT SUM(pi - ca - rb + crb) FROM T1 where a=7 and b=9 and c=2448 and e=1 and g=2 and f IN (SELECT f FROM T2); How can I coax SQLite into selecting/computing from the "view members" without completely rewriting the statement? ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems compiling FTS5 extension
In short the error I get is fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named '__builtin___snprintf_chk' More details in SO question here: https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension, please let me know if I should paste everything in my email. I've seen a similar problem reported before and somehow related to XCode, but that issue was reported to be fixed. Would be thankful for any tips, I haven't used C for years and have no idea where to start. To clarify my goal I need the standalone loadable extension to use with embedded SQLite in my Node.js project. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
