Re: [sqlite] struggling with a query

[RSmith]

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