Re: [sqlite] aggregate functions with DISTINCT

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... >> >>

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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',

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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,

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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,

Re: [sqlite] aggregate functions with DISTINCT

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,

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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 :)

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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

Re: [sqlite] aggregate functions with DISTINCT

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