Re: DISTINCT ON changes sort order on its own it seems - Mailing list pgsql-general

Alexander Reichstadt <info@apfeltaste.net> writes:
> following a query:
> SELECT DISTINCT ON (msgid) msgid FROM (SELECT refid_messages as msgid FROM messagehistorywithcontent WHERE 1=1 AND
(lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle],' ')) LIKE '%gg%') ORDER BY messagekind DESC)
asfoo 

> This query rearranges the sort order though.

It's perfectly within its rights to emit the rows in any order
whatsoever, because you wrote no ORDER BY in the outer query level.
The ORDER BY in the sub-select is not binding on the outer query.

            regards, tom lane