how to

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

how to

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
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to

David Raymond
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to

R Smith-2
In reply to this post by Mark Wagner
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



On 2018/12/04 6:17 AM, Mark Wagner wrote:

> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to

Mark Wagner
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>

On Tue, Dec 4, 2018 at 6:04 AM R Smith <[hidden email]> wrote:

> 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
>
>
>
> On 2018/12/04 6:17 AM, Mark Wagner wrote:
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to

David Raymond
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:[hidden email]] 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>

On Tue, Dec 4, 2018 at 6:04 AM R Smith <[hidden email]> wrote:

> 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
>
>
>
> On 2018/12/04 6:17 AM, Mark Wagner wrote:
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users