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

[sqlite] aggregate functions with DISTINCT

The documentation for aggregate functions states that "In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT". My questions are: Why is DISTINCT only allowed with a single argument in a function like GROUP_CONCAT? Can that limitation be

Re: [sqlite] SQLite aggregate functions by Tcl

Hello! В сообщении от Monday 01 December 2008 18:16:04 D. Richard Hipp написал(а): > The current TCL interface for SQLite does not provide the ability to   > add aggregate functions written in TCL.  So in that sense, it is not   > possible.  However, the TCL interface could be extended to add

Re: [sqlite] SQLite aggregate functions by Tcl

On Dec 1, 2008, at 9:56 AM, Alexey Pechnikov wrote: > Hello! > > Is it possible? > The current TCL interface for SQLite does not provide the ability to add aggregate functions written in TCL. So in that sense, it is not possible. However, the TCL interface could be extended to add this

[sqlite] SQLite aggregate functions by Tcl

Hello! Is it possible? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] aggregate functions and sqlite3_set_auxdata / sqlite3_get_auxdata

I've just figured out that sqlite3_get_auxdata() and sqlite3_set_auxdata() are designed to be used only with scalar user defined functions. If they are used with aggregate functions, they cause sporadic and mysterious segfaults. In particular, VdbeFunc within the context is not initialized for

[sqlite] Aggregate Functions

Hi, I am looking for some documentation alongwith examples on the Aggregate Functions concept in SQLite. Please let me know of some link where this concept is explained and with examples on the functions - sqlite3_create_function() sqlite3_aggregate_context() Thanks & Regards, Ritesh

[sqlite] Aggregate functions and rows returned

I have the following SQL statement: SELECT track_id, MIN(time), tempo FROM tempo_map GROUP BY track_id; And a table tempo_map: 1|0|130 2|0|124 3|0|154 3|20|82 5|0|120 5|10|164 The idea is that the SELECT should find the tempo belonging to the lowest time for each track_id. However, when