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, max(vid=2)-max(vid=1)
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 actually working with (th
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:
SELEC
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)
> from v group by name
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" VA
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 since tyranny's the only
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 thi
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 maili
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 EN
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
> - status: -1 if in version 1 bu
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
11 matches
Mail list logo