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 (select distinct value from test) limit 1;
which seems far more convoluted than just: select group_concat(value) from (select distinct value from test order by value); and select group_concat(value) from (select distinct value from test order by value desc); -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Dominique Devienne >Sent: Monday, 2 March, 2020 08:02 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] How to enforce a specific order of group_concat? > >On Sun, Mar 1, 2020 at 10:58 PM mailing lists <mailingli...@skywind.eu> >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 you want (modulo DISTINCT perhaps). Notably (from the >doc): > >Every aggregate window function can also work as a ordinary aggregate >function, >simply by omitting the OVER and FILTER clauses. Furthermore, all of >the built-in aggregate >functions of SQLite can be used as an aggregate window function by >adding an appropriate OVER clause > >[2] has an example with group_concat() and OVER (ORDER BY ...). I >assume that's what you need, >someone better at Window Functions then me (not difficult!) can >confirm or not that. --DD > >[1] https://www.sqlite.org/windowfunctions.html >[2] https://www.sqlite.org/windowfunctions.html#aggwinfunc >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
