struggling with a query

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

struggling with a query

Stephan Beal-3
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 "v" VALUES(1,'bar');
INSERT INTO "v" VALUES(2,'bar');
INSERT INTO "v" VALUES(2,'baz');

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
- status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
v1.

So the above data set should produce:

foo, -1
bar, 0
baz, 1

My SQL skills fail me miserably, though.

i have no sqlite3 minimum version requirements (am working from the trunk)
and am free to use recursive select if necessary, but my instinct says that
this should be possible with joins and a CASE (for the status).

Any prods in the right direction would be much appreciated,

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

Kevin Martin

On 8 Feb 2014, at 10:03, Stephan Beal <[hidden email]> 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
> - status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
> v1.

Only tried on your example dataset, but try this:

CREATE VIEW answer as
select
    name,
    count(v2) - count(v1) as result
from
    (select
        a.name as name,
        b.name as v1,
        c.name as v2
    from
        v as a    
        left join
            (select name from v where vid = 1) as b
            on a.name = b.name
        left join
            (select name from v where vid = 2) as c
            on a.name = c.name)
group by
    name
order by
    result asc;

Thanks,
Kev
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

R Smith
In reply to this post by Stephan Beal-3
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 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=2;

Running that on your table yields:
VName    "VInd"
bar    0
baz    1
foo    -1

I'm sure someone will have a more succint or optimized version soon :)


On 2014/02/08 12:03, Stephan Beal wrote:

> 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 "v" VALUES(1,'bar');
> INSERT INTO "v" VALUES(2,'bar');
> INSERT INTO "v" VALUES(2,'baz');
>
> 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
> - status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
> v1.
>
> So the above data set should produce:
>
> foo, -1
> bar, 0
> baz, 1
>
> My SQL skills fail me miserably, though.
>
> i have no sqlite3 minimum version requirements (am working from the trunk)
> and am free to use recursive select if necessary, but my instinct says that
> this should be possible with joins and a CASE (for the status).
>
> Any prods in the right direction would be much appreciated,
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

big stone
In reply to this post by Stephan Beal-3
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 mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

Luuk
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 this:

with v(vid,name) as (values (-1,'foo'),(0,'bar'),(1,'baz'))
select * from v

;-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

Stephan Beal-3
In reply to this post by R Smith
On Sat, Feb 8, 2014 at 11:57 AM, RSmith <[hidden email]> 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 since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

Bernd Lehmkuhl
In reply to this post by Stephan Beal-3
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" VALUES(2,'baz');
>
> 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
> - status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
> v1.
>
> So the above data set should produce:
>
> foo, -1
> bar, 0
> baz, 1
>

Should work as well:

SELECT
   name,
   CASE
     WHEN minvid = maxvid AND minvid = 1
     THEN -1
     WHEN minvid = maxvid AND minvid = 2
     THEN 1
     ELSE 0
   END vid
FROM
   (
     SELECT
       name,
       MIN(vid) AS minvid,
       MAX(vid) AS maxvid
     FROM
       v
     GROUP BY
       name
   )

Bernd

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

Stephan Beal-3
In reply to this post by big stone
On Sat, Feb 8, 2014 at 11:58 AM, big stone <[hidden email]> 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
>

i like that one. This slight variation (to allow me to strategically place
the inputs) works for me:


BEGIN TRANSACTION;
DROP TABLE IF EXISTS vf;
CREATE TABLE vf(vid,name);
INSERT INTO "vf" VALUES(1,'foo');
INSERT INTO "vf" VALUES(1,'bar');
INSERT INTO "vf" VALUES(1,'barz');
INSERT INTO "vf" VALUES(2,'bar');
INSERT INTO "vf" VALUES(2,'baz');
INSERT INTO "vf" VALUES(2,'barz');
COMMIT;

with
origin (v1,v2) as (select 1 v1, 2 v2),
v(vid,name) as (select vid,name from vf)
select  name,
         -max(case when vid=origin.v1 then 1 else 0 end )
         + max(case when vid=origin.v2 then 1 else 0 end)
 from v, origin group by name
;

sqlite> .read x.sql
bar|0
barz|0
baz|1
foo|-1

Thank you very much :).

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

R Smith
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:

SELECT name, max(vid=2)-max(vid=1) FROM v GROUP BY name;

So much more succint than my original, like I predicted :)


On 2014/02/08 13:11, Stephan Beal wrote:

> On Sat, Feb 8, 2014 at 11:58 AM, big stone <[hidden email]> 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
>>
> i like that one. This slight variation (to allow me to strategically place
> the inputs) works for me:
>
>
> BEGIN TRANSACTION;
> DROP TABLE IF EXISTS vf;
> CREATE TABLE vf(vid,name);
> INSERT INTO "vf" VALUES(1,'foo');
> INSERT INTO "vf" VALUES(1,'bar');
> INSERT INTO "vf" VALUES(1,'barz');
> INSERT INTO "vf" VALUES(2,'bar');
> INSERT INTO "vf" VALUES(2,'baz');
> INSERT INTO "vf" VALUES(2,'barz');
> COMMIT;
>
> with
> origin (v1,v2) as (select 1 v1, 2 v2),
> v(vid,name) as (select vid,name from vf)
> select  name,
>           -max(case when vid=origin.v1 then 1 else 0 end )
>           + max(case when vid=origin.v2 then 1 else 0 end)
>   from v, origin group by name
> ;
>
> sqlite> .read x.sql
> bar|0
> barz|0
> baz|1
> foo|-1
>
> Thank you very much :).
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

Stephan Beal-3
On Sat, Feb 8, 2014 at 12:39 PM, RSmith <[hidden email]> 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 actually working with (the fossil SCM's vfile
table - my example is a simplified form to help me get my head around the
SQL).

FWIW, sqlite3's ".stats" say yours is overall more efficient:

Virtual Machine Steps:               242
vs the WITH variant i posted:
Virtual Machine Steps:               308


Thanks again!

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: struggling with a query

R Smith
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 <[hidden email]> 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 actually working with (the fossil SCM's vfile
> table - my example is a simplified form to help me get my head around the
> SQL).
>
> FWIW, sqlite3's ".stats" say yours is overall more efficient:
>
> Virtual Machine Steps:               242
> vs the WITH variant i posted:
> Virtual Machine Steps:               308
>
>
> Thanks again!
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users