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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
