I don't recall that any (aggregate) function is concerned at all about the order in which rows are visited. The effect is only visible in non-commutative aggregates (e.g. concatenation).
If you want the arguments presented to an aggregate function in a specific order, then you need to enforce that order, with an order by clause in a subselect if necessary. If you have an order by clause which is already fulfilled by the visitation order, SQLite will not sort again. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Eric Grange Gesendet: Dienstag, 08. Jänner 2019 09:17 An: General Discussion of SQLite Database <[email protected]> Betreff: [EXTERNAL] [sqlite] json_group_array() and sorting Hi, Is json_group_array() supposed to honor a sorting clause or not ? (and concatenation aggregates in general) I have a query like select json_group_array(json_object( 'id', st.id, 'num', st.numeric_field, ...bunch of fields here... )) from some_table st ...bunch of joins here... where ...bunch of conditions... order by st.numeric_field desc limit 50 but the resulting JSON array is not ordered according to the "order by", but AFAICT by the st.id field (a primary key) When not aggregating, the records are in the correct order. Is it a bug or something expected ? I can get the proper order when I use a subquery for the joins & filters, and aggregate in a top level query, but that is rather more verbose, and I am not sure the ordering being preserved in that case is not just 'circumstancial' and could be affected by future SQLite query optimizations. Thanks! Eric _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
