Re: DISTINCT ON changes sort order

Alexander Reichstadt <info@apfeltaste.net> writes:
> I think I solved it:
> SELECT * FROM (SELECT DISTINCT ON(refid_messages) refid_messages as msgid, * FROM messagehistorywithcontent WHERE
(lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle],' ')) LIKE '%gg%') ORDER BY refid_messages
DESC)as foo ORDER BY messagekind ASC 

This is not really a solution, unless you don't care which of the rows
with a particular refid_messages value comes out.  Usually, use of
"DISTINCT ON (x)" should be accompanied by "ORDER BY x, y", where y is
what you want to determine which row of a given x value comes out.
If you write it like this, you get the row with smallest y for each
x; or you could write "ORDER BY x, y DESC" to get the row with largest
y.  See the DISTINCT ON example in the SELECT reference page.

            regards, tom lane