Clemens Ladisch wrote:
>
> John Found wrote:
>> Also, it seems max(b = ?1) will do the trick as well as count(b = ?1)
>>
>> And here another question appears. What is more efficient?
>
> In SQLite, both are equally efficient.
>
> Use whatever makes the query easier to understand.
Clemens, I
John Found wrote:
> Also, it seems max(b = ?1) will do the trick as well as count(b = ?1)
>
> And here another question appears. What is more efficient?
In SQLite, both are equally efficient.
Use whatever makes the query easier to understand.
Regards,
Clemens
On 2018/10/12 7:38 AM, John Found wrote:
The following code does not work, but gives an idea what I want to do:
create table t (a, b);
select
group_concat(b) as list
from t
group by a
having ?1 in (list);
i.e. how to select only the groups that contain
some
On Fri, 12 Oct 2018 11:12:17 +0200
Clemens Ladisch wrote:
> I wrote:
> > But you need to find some aggregate function that can do the filtering.
>
> HAVING SUM(b = ?1)
>
> (In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END)
> > 0")
Hey, this really looks great.
> Op 12 okt. 2018, om 11:23 heeft Clemens Ladisch het
> volgende geschreven:
>
> E.Pasma wrote:
>> select group_concat(b) as list
>> from t
>> group by a
>> having count(b=?1)
>> ;
>
> In SQLite, a boolean expression returns 0 when false, and count(0) is 1.
> You have to generate a NULL for
E.Pasma wrote:
> select group_concat(b) as list
> from t
> group by a
> having count(b=?1)
> ;
In SQLite, a boolean expression returns 0 when false, and count(0) is 1.
You have to generate a NULL for failed matches, or use another function
like sum() or max() that can filter out zeros.
Regards,
> John Found :
>
> The following code does not work, but gives an idea what I want to do:
>
>create table t (a, b);
>
>select
> group_concat(b) as list
>from t
>group by a
>having ?1 in (list);
>
> Clemens Ladisch :
>
> select
>group_concat(b) as list
>
I wrote:
> But you need to find some aggregate function that can do the filtering.
HAVING SUM(b = ?1)
(In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) >
0")
Regards,
Clemens
___
sqlite-users mailing list
John Found wrote:
> Clemens Ladisch wrote:
>> select
>> group_concat(b) as list
>> from t
>> where a in (select a
>> from t
>> where b = ?1)
>> group by a;
>>
>> But you will not be able to avoid the subquery: the filter has to
>> include all other rows of
On Friday, 12 October, 2018 01:02, John Found wrote:
>Hm, is sounds strange because when HAVING clause is processed,
>the aggregate functions should not be processed yet (for a
>performance reasons) i.e. the query still has access to all
>values from the field
>b
>and theoretically should be
On Fri, 12 Oct 2018 08:27:10 +0200
Clemens Ladisch wrote:
> John Found wrote:
> > i.e. how to select only the groups that contain
> > some value in the set of values in a column not
> > specified in group by clause.
> >
> > select
> > (select group_concat(b) from t t1 where t1.a =
John Found wrote:
> i.e. how to select only the groups that contain
> some value in the set of values in a column not
> specified in group by clause.
>
> select
> (select group_concat(b) from t t1 where t1.a = t2.a) as list
> from t t2
> where b = ?1;
Similarly:
select
The following code does not work, but gives an idea what I want to do:
create table t (a, b);
select
group_concat(b) as list
from t
group by a
having ?1 in (list);
i.e. how to select only the groups that contain
some value in the set of values in a column not
13 matches
Mail list logo
