Re: [sqlite] struggling with a query

Just to be clear, it isn't really "mine", just an adaption of the many excellent contributions, from which I too have learned. A huge pleasure and fun exercise no less! On 2014/02/08 14:35, Stephan Beal wrote: On Sat, Feb 8, 2014 at 12:39 PM, RSmith wrote: SELECT name,

Re: [sqlite] struggling with a query

On Sat, Feb 8, 2014 at 12:39 PM, RSmith wrote: > SELECT name, max(vid=2)-max(vid=1) FROM v GROUP BY name; > > So much more succint than my original, like I predicted :) Indeed!!! This one wins if i am able to refactor it for use with the much more complex structure i'm

Re: [sqlite] struggling with a query

Yeah I quite like some of the solutions posted - got to love this list :) One final optimization, since those values you are looking for essentially maps to Boolean (0 and 1), this query is the smallest and probably fastest (I think) that will produce the correct results from your table:

Re: [sqlite] struggling with a query

On Sat, Feb 8, 2014 at 11:58 AM, big stone wrote: > with sqlite 3.8.3 (for the with) : > > with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz')) > > select name, > -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2 > then 1 else 0 end) >

Re: [sqlite] struggling with a query

Am 08.02.2014 11:03, schrieb Stephan Beal: i have table containing a mapping of logic dataset versions and filenames contained in that dataset version: CREATE TABLE v(vid,name); INSERT INTO "v" VALUES(1,'foo'); INSERT INTO "v" VALUES(1,'bar'); INSERT INTO "v" VALUES(2,'bar'); INSERT INTO "v"

Re: [sqlite] struggling with a query

On Sat, Feb 8, 2014 at 11:57 AM, RSmith wrote: > One way of doing it: > Many thanks to you and Kevin both! These examples give me plenty to study for today :). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But

Re: [sqlite] struggling with a query

On 08-02-2014 11:58, big stone wrote: with sqlite 3.8.3 (for the with) : with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz')) select name, -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2 then 1 else 0 end) from v group by name almost the same as

Re: [sqlite] struggling with a query

with sqlite 3.8.3 (for the with) : with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz')) select name, -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2 then 1 else 0 end) from v group by name ___ sqlite-users

Re: [sqlite] struggling with a query

One way of doing it: SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE -1 END AS VInd FROM v AS V1 LEFT JOIN v AS V2 ON (V1.vid<>V2.vid) AND (V1.name=V2.name) WHERE V1.vid=1 UNION SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE 1

Re: [sqlite] struggling with a query

On 8 Feb 2014, at 10:03, Stephan Beal wrote: > i am trying like mad to, but can't seem formulate a query with 2 version > number inputs (1 and 2 in this case) and creates a result set with these > columns: > > - name. must include all names across both versions > -

[sqlite] struggling with a query

Hi, list, most of the time i judge my SQL skills as mediocre, but at times like this i feel like a complete noob... i have table containing a mapping of logic dataset versions and filenames contained in that dataset version: CREATE TABLE v(vid,name); INSERT INTO "v" VALUES(1,'foo'); INSERT INTO