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

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

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

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

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),

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

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

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

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

[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

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:

[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

[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