[sqlite] how to

Discussion:

[sqlite] how to

Mark Wagner

2018-12-04 04:17:18 UTC

Given a table with two columns, A and B, with no constraints what would be
the best way to query for those values of A such that there are
corresponding values of B in a specified set.

For example, given this data, below, and ignoring the primary key, I would
want the following results:

for p values 10,11,12 ==> 1
for p values 11,12 ==> 2
for p values 10 ==> 3

For all other "input" we should get no result/null/whatever.

CREATE TABLE t (id integer primary key, c, p);

INSERT INTO t VALUES(1, 1,10);

INSERT INTO t VALUES(2, 1,11);

INSERT INTO t VALUES(3, 1,12);

INSERT INTO t VALUES(4, 2,11);

INSERT INTO t VALUES(5, 2,12);

INSERT INTO t VALUES(6, 3,10);

For all other "input" we should get no result/null/whatever.

I can concoct a query based on the "input" like this but it seems like
there must be a better way?

SELECT DISTINCT c as C FROM t WHERE
EXISTS (SELECT c FROM t AS x WHERE x.p = 10)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12)

AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3;

David Raymond

2018-12-04 13:41:55 UTC

Permalink

So reading your example I think you mean

Given a table with two columns, c and p, with no constraints what would be
the best way to query for those values of c such that
the set of corresponding values of p is equal to a specified set.

If p isn't text then I'd say something using group_concat

select c from t group by c having group_concat(p, '|') = '10|11|12';

Only problem being that group_concat is non-deterministic(?) in the sense that the order in which it gets the values matters, and while you might get it to work with an "implementation detail" of a specific release to give it the values in the right order, it's not in spec and might change.

So ...from (select * from t order by p) group by c ... might work now, but also might not work later.

I "think" there's a way to do it with the fancy new window function functionality which will guarantee ordered, but haven't figured that out yet.

On a tangent: There are no links to the "Window Functions" page (https://www.sqlite.org/windowfunctions.html) from any of the following at the moment:
"SQLite Documentation": (https://www.sqlite.org/docs.html)
"Query Language Understood by SQLite": (https://www.sqlite.org/lang.html)
"SQLite Query Language: SELECT": (https://www.sqlite.org/lang_select.html)

There's only a single, one word link on the Expression page (https://www.sqlite.org/lang_expr.html)
Would it be feasable to get some links added in some of those spots?

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] On Behalf Of Mark Wagner
Sent: Monday, December 03, 2018 11:17 PM
To: SQLite mailing list
Subject: [sqlite] how to

Given a table with two columns, A and B, with no constraints what would be
the best way to query for those values of A such that there are
corresponding values of B in a specified set.

For example, given this data, below, and ignoring the primary key, I would
want the following results:

for p values 10,11,12 ==> 1
for p values 11,12 ==> 2
for p values 10 ==> 3

For all other "input" we should get no result/null/whatever.

CREATE TABLE t (id integer primary key, c, p);

INSERT INTO t VALUES(1, 1,10);

INSERT INTO t VALUES(2, 1,11);

INSERT INTO t VALUES(3, 1,12);

INSERT INTO t VALUES(4, 2,11);

INSERT INTO t VALUES(5, 2,12);

INSERT INTO t VALUES(6, 3,10);

For all other "input" we should get no result/null/whatever.

I can concoct a query based on the "input" like this but it seems like
there must be a better way?

SELECT DISTINCT c as C FROM t WHERE
EXISTS (SELECT c FROM t AS x WHERE x.p = 10)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12)

AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3;
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

R Smith

2018-12-04 14:04:44 UTC

Permalink

I've mixed up the adding orders to make sure they have no affect on the
outcome - and in the final results examples I've left all the columns so
you can see what is going on, but you of course need only one of the
columns in your desired output.

CREATE TABLE t (id integer primary key, c, p);
INSERT INTO t VALUES(1, 1,11);
INSERT INTO t VALUES(2, 1,12);
INSERT INTO t VALUES(3, 1,10);
INSERT INTO t VALUES(4, 2,11);
INSERT INTO t VALUES(5, 2,12);
INSERT INTO t VALUES(6, 3,10);

-- Base Query:
WITH SETS(PSet, PContent, PCount) AS (
    SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
;

--     PSet     | PContent | PCount
-- ------------ | -------- | ------
--       1      | 10,11,12 |    3
--       2      |   11,12 |    2
--       3      |    10    |    1

-- Example one - finding the set that contains all of 10,11,12 and
nothing else:
WITH SETS(PSet, PContent, PCount) AS (
    SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
WHERE PContent = '10,11,12'
;

--     PSet     |   PContent   |    PCount
-- ------------ | ------------ | ------------
--       1      |   10,11,12   |       3

-- Example two: Finding any set that contains 11,12:
WITH SETS(PSet, PContent, PCount) AS (
    SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
WHERE PContent LIKE '%11,12%'
;

--     PSet     | PContent | PCount
-- ------------ | -------- | ------
--       1      | 10,11,12 |    3
--       2      |   11,12 |    2

Post by Mark Wagner
Given a table with two columns, A and B, with no constraints what would be
the best way to query for those values of A such that there are
corresponding values of B in a specified set.
For example, given this data, below, and ignoring the primary key, I would
for p values 10,11,12 ==> 1
for p values 11,12 ==> 2
for p values 10 ==> 3
For all other "input" we should get no result/null/whatever.
CREATE TABLE t (id integer primary key, c, p);
INSERT INTO t VALUES(1, 1,10);
INSERT INTO t VALUES(2, 1,11);
INSERT INTO t VALUES(3, 1,12);
INSERT INTO t VALUES(4, 2,11);
INSERT INTO t VALUES(5, 2,12);
INSERT INTO t VALUES(6, 3,10);
For all other "input" we should get no result/null/whatever.
I can concoct a query based on the "input" like this but it seems like
there must be a better way?
SELECT DISTINCT c as C FROM t WHERE
EXISTS (SELECT c FROM t AS x WHERE x.p = 10)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12)
AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3;
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Mark Wagner

2018-12-04 16:33:56 UTC

Permalink

Prior to my original message I was playing around with group concat (even
though I know the order is considered arbitrary) but found I couldn't put a
where clause on that column.

Is there anything wrong with this code?

sqlite> select c, group_concat(p) as P from t group by c;
c P
---------- ----------
1 10,11,12
2 11,12
3 10

BUT...

select c, group_concat(p) as P from t where P='10,11,12' group by c ;
<no results>

select c, group_concat(p) as P from t where P='10' group by c ;
<no results>

Post by R Smith
I've mixed up the adding orders to make sure they have no affect on the
outcome - and in the final results examples I've left all the columns so
you can see what is going on, but you of course need only one of the
columns in your desired output.
CREATE TABLE t (id integer primary key, c, p);
INSERT INTO t VALUES(1, 1,11);
INSERT INTO t VALUES(2, 1,12);
INSERT INTO t VALUES(3, 1,10);
INSERT INTO t VALUES(4, 2,11);
INSERT INTO t VALUES(5, 2,12);
INSERT INTO t VALUES(6, 3,10);
WITH SETS(PSet, PContent, PCount) AS (
SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
;
-- PSet | PContent | PCount
-- ------------ | -------- | ------
-- 1 | 10,11,12 | 3
-- 2 | 11,12 | 2
-- 3 | 10 | 1
-- Example one - finding the set that contains all of 10,11,12 and
WITH SETS(PSet, PContent, PCount) AS (
SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
WHERE PContent = '10,11,12'
;
-- PSet | PContent | PCount
-- ------------ | ------------ | ------------
-- 1 | 10,11,12 | 3
WITH SETS(PSet, PContent, PCount) AS (
SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER
BY c,p) GROUP BY c
)
SELECT *
FROM SETS
WHERE PContent LIKE '%11,12%'
;
-- PSet | PContent | PCount
-- ------------ | -------- | ------
-- 1 | 10,11,12 | 3
-- 2 | 11,12 | 2

Post by Mark Wagner
Given a table with two columns, A and B, with no constraints what would

Post by Mark Wagner
the best way to query for those values of A such that there are
corresponding values of B in a specified set.
For example, given this data, below, and ignoring the primary key, I

would

Post by Mark Wagner
for p values 10,11,12 ==> 1
for p values 11,12 ==> 2
for p values 10 ==> 3
For all other "input" we should get no result/null/whatever.
CREATE TABLE t (id integer primary key, c, p);
INSERT INTO t VALUES(1, 1,10);
INSERT INTO t VALUES(2, 1,11);
INSERT INTO t VALUES(3, 1,12);
INSERT INTO t VALUES(4, 2,11);
INSERT INTO t VALUES(5, 2,12);
INSERT INTO t VALUES(6, 3,10);
For all other "input" we should get no result/null/whatever.
I can concoct a query based on the "input" like this but it seems like
there must be a better way?
SELECT DISTINCT c as C FROM t WHERE
EXISTS (SELECT c FROM t AS x WHERE x.p = 10)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11)
AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12)
AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3;
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

David Raymond

2018-12-04 16:42:01 UTC

Permalink

WHERE filtering happens before aggregates, so you can't use it on an aggregate column, only a HAVING clause works for that. Or you need to make it a sub-query and surround it with another select to get the where

so something like...
select c, group_concat(p) as P from t group by c having P = '10';

...or if it doesn't recognize P, then
select c, group_concat(p) as P from t group by c having group_concat(p) = '10';

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] On Behalf Of Mark Wagner
Sent: Tuesday, December 04, 2018 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] how to

Prior to my original message I was playing around with group concat (even
though I know the order is considered arbitrary) but found I couldn't put a
where clause on that column.

Is there anything wrong with this code?

sqlite> select c, group_concat(p) as P from t group by c;
c P
---------- ----------
1 10,11,12
2 11,12
3 10

BUT...

select c, group_concat(p) as P from t where P='10,11,12' group by c ;
<no results>

select c, group_concat(p) as P from t where P='10' group by c ;
<no results>

Post by Mark Wagner
Given a table with two columns, A and B, with no constraints what would

would

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users