Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-03 Thread Dominique Devienne
On Mon, Mar 2, 2020 at 6:35 PM Keith Medcalf wrote: > Well, in theory an order by in a nested select means that the result of the > operation is an ordered projection and not merely a set of rows. > For this particular case (a nested select with an order by and the outer > query with an

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Keith Medcalf
On Monday, 2 March, 2020 09:20, Dominique Devienne wrote: >On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf wrote: >> select group_concat(value) from (select distinct value from test order by >> value); >But is that guaranteed to be ordered correctly "forever" instead of by >"happenstance" from

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Dominique Devienne
On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf wrote: > select group_concat(value) from (select distinct value from test order by > value); But is that guaranteed to be ordered correctly "forever" instead of by "happenstance" from current implementation details? My point was that the Window

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Keith Medcalf
You mean like: select group_concat(value) over (order by value rows between unbounded preceding and unbounded following) from (select distinct value from test) limit 1; and select group_concat(value) over (order by value desc rows between unbounded preceding and unbounded following) from

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Dominique Devienne
On Sun, Mar 1, 2020 at 10:58 PM mailing lists wrote: > Are there any other solutions / possibilities? I thought someone more knowledgeable than I about Window Functions [1] would answer, but since nobody mentioned them so far, I'll do it, as I believe this is the "SQL native" way to achieve what

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread Jean-Luc Hainaut
On 1/03/2020 22:57, mailing lists wrote: Assume I create the following table: CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); INSERT INTO Test (Value) VALUES('Alpha'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value)

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-02 Thread mailing lists
Hi Keith, thanks for the explanation. PS: I used a CTE because official examples (e.g. Mandelbrot) also used CTEs in combination with group_concat. Although the incorporation of group_concat was not the primary reason to use CTEs. PPS: Is it possible to rephrase the documentation for

Re: [sqlite] How to enforce a specific order of group_concat?

2020-03-01 Thread Keith Medcalf
On Sunday, 1 March, 2020 14:58, mailing lists wrote: >Assume I create the following table: >CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); >INSERT INTO Test (Value) VALUES('Alpha'); >INSERT INTO Test (Value) VALUES('Beta'); >INSERT INTO Test (Value) VALUES('Beta'); >INSERT INTO Test

[sqlite] How to enforce a specific order of group_concat?

2020-03-01 Thread mailing lists
Assume I create the following table: CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); INSERT INTO Test (Value) VALUES('Alpha'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Alpha'); According to the documentation of