On Sat, Feb 8, 2014 at 11:58 AM, big stone <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
