Re: [sqlite] aggregate functions with DISTINCT

[Clemens Ladisch]

Dominique Devienne wrote:
> On Mon, Jan 12, 2015 at 4:13 PM, Igor Tandetnik  wrote:
>> On 1/12/2015 9:53 AM, Dominique Devienne wrote:
>>> My little brain has no idea how the "a;b:c/c,d" came about from the input
>>> rows, so I don't find it logical at all myself...
>>
>> Simple, really. For each ('x', '@') row, string_agg adds '@x' to the
>> resulting string (except the separator is omitted for the very first row
>> encountered, in this case ('a', ',') ). Thanks to DISTINCT, the second
>> occurrence of ('c', '/') is skipped , so you only have '/c' appear once. On
>> the other hand, both '/c' and ':c' appear, indicating that DISTINCT works
>> on both parameters, not just the first.
>
> OK, lets follow that logic:
> 1) a-comma yield just a (first row special case)
> 2) b-semi-colon yields semi-colon b
> 3) c-slash (both of them, collapsed by DISTINCT) yields slash c
> 4) c-colon yields colon c
> 5) d comma yields comma d
>
> so that's "a;b/c:c,d", not "a;b:c/c,d"

To remove duplicates efficiently, the database sorts the values, and the
colon happens to be sorted before the slash.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Igor Tandetnik]

On 1/12/2015 10:37 AM, Dominique Devienne wrote:

OK, lets follow that logic:
1) a-comma yield just a (first row special case)
2) b-semi-colon yields semi-colon b
3) c-slash (both of them, collapsed by DISTINCT) yields slash c
4) c-colon yields colon c
5) d comma yields comma d

so that's "a;b/c:c,d", not "a;b:c/c,d"


Again, aggregate functions don't guarantee any particular order of 
visiting the rows. In this example, the function happens to visit ':c' 
first and '/c' second.



  But I fail to see how the delimiter come from in "a;b:c/c,d", especially

why they are "out of order" vs the value-order. FWIW. --DD


Aggregate functions don't guarantee any particular order of visiting rows
within the group. What is this "value-order" of which you speak? I'm not
familiar with the term.


Just order of the "value" column in Clemens example.


What do you mean by "order of the "value" column"? The order in which 
rows happened to be inserted into the table? Why should that order 
matter for anything?

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Dominique Devienne]

On Mon, Jan 12, 2015 at 4:13 PM, Igor Tandetnik  wrote:

> On 1/12/2015 9:53 AM, Dominique Devienne wrote:
>
>> My little brain has no idea how the "a;b:c/c,d" came about from the input
>> rows, so I don't find it logical at all myself...
>>
>
> Simple, really. For each ('x', '@') row, string_agg adds '@x' to the
> resulting string (except the separator is omitted for the very first row
> encountered, in this case ('a', ',') ). Thanks to DISTINCT, the second
> occurrence of ('c', '/') is skipped , so you only have '/c' appear once. On
> the other hand, both '/c' and ':c' appear, indicating that DISTINCT works
> on both parameters, not just the first.


OK, lets follow that logic:
1) a-comma yield just a (first row special case)
2) b-semi-colon yields semi-colon b
3) c-slash (both of them, collapsed by DISTINCT) yields slash c
4) c-colon yields colon c
5) d comma yields comma d

so that's "a;b/c:c,d", not "a;b:c/c,d"


>  But I fail to see how the delimiter come from in "a;b:c/c,d", especially
>> why they are "out of order" vs the value-order. FWIW. --DD
>>
>
> Aggregate functions don't guarantee any particular order of visiting rows
> within the group. What is this "value-order" of which you speak? I'm not
> familiar with the term.


Just order of the "value" column in Clemens example. Not value in general.
OK, the no-order-guarantee would explain the difference with my result
above. I think I see it now. Thanks, Igor. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Igor Tandetnik]

On 1/12/2015 9:53 AM, Dominique Devienne wrote:

My little brain has no idea how the "a;b:c/c,d" came about from the input
rows, so I don't find it logical at all myself...


Simple, really. For each ('x', '@') row, string_agg adds '@x' to the 
resulting string (except the separator is omitted for the very first row 
encountered, in this case ('a', ',') ). Thanks to DISTINCT, the second 
occurrence of ('c', '/') is skipped , so you only have '/c' appear once. 
On the other hand, both '/c' and ':c' appear, indicating that DISTINCT 
works on both parameters, not just the first.



But I fail to see how the delimiter come from in "a;b:c/c,d", especially
why they are "out of order" vs the value-order. FWIW. --DD


Aggregate functions don't guarantee any particular order of visiting 
rows within the group. What is this "value-order" of which you speak? 
I'm not familiar with the term.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Dominique Devienne]

On Mon, Jan 12, 2015 at 2:30 PM, Richard Hipp  wrote:

> Certainly the group_concat() case you cite, with a constant second
> term makes sense.  But any code I write needs to work *in general*,
> and not for just that one special case.
>

Is it not possible to detect the "constant" in the parsing or semantic
analysis phase?

Are there no other example in SQLite where the "variable" vs "constant"
expression matters? --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Dominique Devienne]

On Mon, Jan 12, 2015 at 3:36 PM, Staffan Tylen 
wrote:

> On Mon, Jan 12, 2015 at 3:25 PM, Clemens Ladisch 
> wrote:
>
> > Testing  shows that the DISTINCT
> > applies to the function, not to individual parameters, and that indeed
> > the set of all parameters is checked:
>


> This is to me the most logical way to look at it, DISTINCT should apply to
> the result regardless of the number of parameters required to create it.
>

My little brain has no idea how the "a;b:c/c,d" came about from the input
rows, so I don't find it logical at all myself...

I see what the distinct set of (value), or (delim), or (value,delim) tuples
are.

But I fail to see how the delimiter come from in "a;b:c/c,d", especially
why they are "out of order" vs the value-order. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Staffan Tylen]

On Mon, Jan 12, 2015 at 3:25 PM, Clemens Ladisch  wrote:

> Testing  shows that the DISTINCT
> applies to the function, not to individual parameters, and that indeed
> the set of all parameters is checked:
>
>
This is to me the most logical way to look at it, DISTINCT should apply to
the result regardless of the number of parameters required to create it.

Staffan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Dominique Devienne]

On Mon, Jan 12, 2015 at 2:30 PM, Richard Hipp  wrote:

> Are there any other SQL database engines that allow multi-argument
> aggregate functions?  What do those systems do?


The "that take a single argument" fragment seems to imply DISTINCT is not
allowed my multi-args aggregate functions. --DD

>From https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035

Many (but not all) aggregate functions that take a single argument accept
these clauses:
* DISTINCT [...] cause an aggregate function to consider only distinct
values of the argument expression. [..]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Clemens Ladisch]

Richard Hipp wrote:
> So if you say "group_concat(DISTINCT x,y)" does that mean that the
> combination of x and y must be distinct or that only x is distinct?
> Are we allowed to say "group_concat(x, DISTINCT y)" or
> "group_concat(DISTINCT x, DISTINCT y)".  And what does the latter
> mean, exactly?
>
> Are there any other SQL database engines that allow multi-argument
> aggregate functions?  What do those systems do?

PostgreSQL says 
:
| aggregate_name (expression [ , ... ] [ order_by_clause ] )
| aggregate_name (ALL expression [ , ... ] [ order_by_clause ] )
| aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] )
|
| The first form of aggregate expression invokes the aggregate once for
| each input row. The second form is the same as the first, since ALL is
| the default. The third form invokes the aggregate once for each
| distinct value of the expression (or distinct set of values, for
| multiple expressions) found in the input rows.

Testing  shows that the DISTINCT
applies to the function, not to individual parameters, and that indeed
the set of all parameters is checked:

  CREATE TABLE test (
value VARCHAR(10),
delim VARCHAR(1)
  );
  INSERT INTO test VALUES ('a', ',');
  INSERT INTO test VALUES ('b', ';');
  INSERT INTO test VALUES ('c', '/');
  INSERT INTO test VALUES ('c', '/');
  INSERT INTO test VALUES ('c', ':');
  INSERT INTO test VALUES ('d', ',');

  SELECT string_agg(DISTINCT value, delim) FROM test;

=>

  a;b:c/c,d


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Richard Hipp]

On 1/12/15, Staffan Tylen  wrote:
> Thanks Clemens. I notice however that nobody has attempted to respond to my
> original questions. I'm mostly interested in why the documentation states
> that DISTINCT can only be used with aggregate functions taking a single
> argument, which in fact is only one, namely GROUP_CONCAT. For a person like
> me with no knowledge of the internals of SQLite it's somewhat puzzling that
> GROUP_CONCAT(DISTINCT ABC) is valid but GROUP_CONCAT(DISCRETE ABC,' ') is
> not. The internal logic must/should IMHO be identical in both cases. It
> doesn't make sense to me. Anyone?
>

DISTINCT is restricted to single-argument aggregates because I do not
know what to do (in general) for broader cases.

Certainly the group_concat() case you site, with a constant second
term makes sense.  But any code I write needs to work *in general*,
and not for just that one special case.

So if you say "group_concat(DISTINCT x,y)" does that mean that the
combination of x and y must be distinct or that only x is distinct?
Are we allowed to say "group_concat(x, DISTINCT y)" or
"group_concat(DISTINCT x, DISTINCT y)".  And what does the latter
mean, exactly?

Are there any other SQL database engines that allow multi-argument
aggregate functions?  What do those systems do?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Staffan Tylen]

Thanks Clemens. I notice however that nobody has attempted to respond to my
original questions. I'm mostly interested in why the documentation states
that DISTINCT can only be used with aggregate functions taking a single
argument, which in fact is only one, namely GROUP_CONCAT. For a person like
me with no knowledge of the internals of SQLite it's somewhat puzzling that
GROUP_CONCAT(DISTINCT ABC) is valid but GROUP_CONCAT(DISCRETE ABC,' ') is
not. The internal logic must/should IMHO be identical in both cases. It
doesn't make sense to me. Anyone?

Staffan


On Sun, Jan 11, 2015 at 2:22 PM, Clemens Ladisch  wrote:

> Staffan Tylen wrote:
> > Well, the SELECT is actually over 400 lines long [...]  I
> > can't use SELECT DISTINCT X as that wouldn't give the result I want, and
> I
> > can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work"
> comment
> > doesn't actually hold. I see no other way than to use DISTINCT with the
> > GROUP_CONCAT function, which in this case is invalid.
>
> You could move the aggregate function into a correlated subquery:
>
>   SELECT x,
>  GROUP_CONCAT(y)
>   FROM T1
>   GROUP BY x
>
>   =>
>
>   SELECT x,
>  (SELECT GROUP_CONCAT(y)
>   FROM T1 as T1_inner
>   WHERE T1_inner.x = T1.x)
>   FROM T1
>   GROUP BY x
>
> This requires duplicating all the joins and WHEREs from the outer query,
> but allows you to handle the inner query differently:
>
>   SELECT x,
>  (SELECT GROUP_CONCAT(y, ' ')
>   FROM (SELECT DISTINCT y
> FROM T1 as T1_inner
> WHERE T1_inner.x = T1.x))
>   FROM T1
>   GROUP BY x
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Clemens Ladisch]

Staffan Tylen wrote:
> Well, the SELECT is actually over 400 lines long [...]  I
> can't use SELECT DISTINCT X as that wouldn't give the result I want, and I
> can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work" comment
> doesn't actually hold. I see no other way than to use DISTINCT with the
> GROUP_CONCAT function, which in this case is invalid.

You could move the aggregate function into a correlated subquery:

  SELECT x,
 GROUP_CONCAT(y)
  FROM T1
  GROUP BY x

  =>

  SELECT x,
 (SELECT GROUP_CONCAT(y)
  FROM T1 as T1_inner
  WHERE T1_inner.x = T1.x)
  FROM T1
  GROUP BY x

This requires duplicating all the joins and WHEREs from the outer query,
but allows you to handle the inner query differently:

  SELECT x,
 (SELECT GROUP_CONCAT(y, ' ')
  FROM (SELECT DISTINCT y
FROM T1 as T1_inner
WHERE T1_inner.x = T1.x))
  FROM T1
  GROUP BY x


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Staffan Tylen]

Just to add: my questions were - why this limitation with DISTINCT, and can
it be changed at least for GROUP_CONCAT?

On Sun, Jan 11, 2015 at 1:23 AM, Staffan Tylen 
wrote:

> Well, the SELECT is actually over 400 lines long so 'visualizing' it
> wouldn't be very easy :) But it's along these lines:
>
> SELECT X FROM
> (SELECT 'ABC'||
>  IFNULL(' PARM('||GROUP_CONCAT(COL1,' ')||')'),' ')||
> etc
> FROM T1
> LEFT JOIN T2
> LEFT JOIN T3
> etc etc (lots of joins)
> UNION
> SELECT 'DEF'||
> etc
> UNION
> etc
> )
>
> So in this case COL1 might contain duplicates that need to be filtered. I
> can't use SELECT DISTINCT X as that wouldn't give the result I want, and I
> can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work" comment
> doesn't actually hold. I see no other way than to use DISTINCT with the
> GROUP_CONCAT function, which in this case is invalid.
>
> Staffan
>
>
>
>
>
> On Sun, Jan 11, 2015 at 1:00 AM, John McKown  > wrote:
>
>> On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen 
>> wrote:
>>
>> > Thanks Richard but unfortunately I cannot use DISTINCT in that way,
>> since
>> > it affects all the selected columns and they are MANY.
>> >
>> > Staffan
>> >
>>
>> ​I am having trouble visualizing what your actual SELECT is. Would you
>> mind
>> posting it?​
>>
>>
>>
>> >
>> >
>> > On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
>> >
>> > > On 1/10/15, Staffan Tylen  wrote:
>> > > > I'm in the situation where I need to use GROUP_CONCAT and filter out
>> > > > duplicates at the same time. And the default comma separator in
>> > > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
>> > function
>> > > > REPLACE to get rid of the comma but only to realise that the data
>> being
>> > > > concatenated also might contain one or more commas.
>> > > >
>> > >
>> > > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab
>> ORDER
>> > > BY 1);
>> > >
>> > >
>> > > --
>> > > D. Richard Hipp
>> > > d...@sqlite.org
>> > > ___
>> > > sqlite-users mailing list
>> > > sqlite-users@sqlite.org
>> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> > >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> ​
>> While a transcendent vocabulary is laudable, one must be eternally careful
>> so that the calculated objective of communication does not become
>> ensconced
>> in obscurity.  In other words, eschew obfuscation.
>>
>> 111,111,111 x 111,111,111 = 12,345,678,987,654,321
>>
>> Maranatha! <><
>> John McKown
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Staffan Tylen]

Well, the SELECT is actually over 400 lines long so 'visualizing' it
wouldn't be very easy :) But it's along these lines:

SELECT X FROM
(SELECT 'ABC'||
 IFNULL(' PARM('||GROUP_CONCAT(COL1,' ')||')'),' ')||
etc
FROM T1
LEFT JOIN T2
LEFT JOIN T3
etc etc (lots of joins)
UNION
SELECT 'DEF'||
etc
UNION
etc
)

So in this case COL1 might contain duplicates that need to be filtered. I
can't use SELECT DISTINCT X as that wouldn't give the result I want, and I
can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work" comment
doesn't actually hold. I see no other way than to use DISTINCT with the
GROUP_CONCAT function, which in this case is invalid.

Staffan





On Sun, Jan 11, 2015 at 1:00 AM, John McKown 
wrote:

> On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen 
> wrote:
>
> > Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
> > it affects all the selected columns and they are MANY.
> >
> > Staffan
> >
>
> ​I am having trouble visualizing what your actual SELECT is. Would you mind
> posting it?​
>
>
>
> >
> >
> > On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
> >
> > > On 1/10/15, Staffan Tylen  wrote:
> > > > I'm in the situation where I need to use GROUP_CONCAT and filter out
> > > > duplicates at the same time. And the default comma separator in
> > > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
> > function
> > > > REPLACE to get rid of the comma but only to realise that the data
> being
> > > > concatenated also might contain one or more commas.
> > > >
> > >
> > > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab
> ORDER
> > > BY 1);
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> ​
> While a transcendent vocabulary is laudable, one must be eternally careful
> so that the calculated objective of communication does not become ensconced
> in obscurity.  In other words, eschew obfuscation.
>
> 111,111,111 x 111,111,111 = 12,345,678,987,654,321
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[John McKown]

On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen 
wrote:

> Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
> it affects all the selected columns and they are MANY.
>
> Staffan
>

​I am having trouble visualizing what your actual SELECT is. Would you mind
posting it?​



>
>
> On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
>
> > On 1/10/15, Staffan Tylen  wrote:
> > > I'm in the situation where I need to use GROUP_CONCAT and filter out
> > > duplicates at the same time. And the default comma separator in
> > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
> function
> > > REPLACE to get rid of the comma but only to realise that the data being
> > > concatenated also might contain one or more commas.
> > >
> >
> > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER
> > BY 1);
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
​
While a transcendent vocabulary is laudable, one must be eternally careful
so that the calculated objective of communication does not become ensconced
in obscurity.  In other words, eschew obfuscation.

111,111,111 x 111,111,111 = 12,345,678,987,654,321

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Staffan Tylen]

Hmm, maybe I'm wrong there, it would remove duplicates of the entire
collection combined, wouldn't it? Yes, it might work.

Staffan


On Sun, Jan 11, 2015 at 12:56 AM, Staffan Tylen 
wrote:

> Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
> it affects all the selected columns and they are MANY.
>
> Staffan
>
>
> On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:
>
>> On 1/10/15, Staffan Tylen  wrote:
>> > I'm in the situation where I need to use GROUP_CONCAT and filter out
>> > duplicates at the same time. And the default comma separator in
>> > GROUP_CONCAT needs to be replaced by a space. I've tried to use function
>> > REPLACE to get rid of the comma but only to realise that the data being
>> > concatenated also might contain one or more commas.
>> >
>>
>> SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER
>> BY 1);
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Staffan Tylen]

Thanks Richard but unfortunately I cannot use DISTINCT in that way, since
it affects all the selected columns and they are MANY.

Staffan


On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp  wrote:

> On 1/10/15, Staffan Tylen  wrote:
> > I'm in the situation where I need to use GROUP_CONCAT and filter out
> > duplicates at the same time. And the default comma separator in
> > GROUP_CONCAT needs to be replaced by a space. I've tried to use function
> > REPLACE to get rid of the comma but only to realise that the data being
> > concatenated also might contain one or more commas.
> >
>
> SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER
> BY 1);
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate functions with DISTINCT

[Richard Hipp]

On 1/10/15, Staffan Tylen  wrote:
> I'm in the situation where I need to use GROUP_CONCAT and filter out
> duplicates at the same time. And the default comma separator in
> GROUP_CONCAT needs to be replaced by a space. I've tried to use function
> REPLACE to get rid of the comma but only to realise that the data being
> concatenated also might contain one or more commas.
>

SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab ORDER BY 1);


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users