Re: DISTINCT ON changes sort order

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

Thanks
Alex

On 24.04.2013, at 16:03, Alexander Reichstadt wrote:

> Hi,
>
> 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) as foo
>
> This query rearranges the sort order though.
>
> When I only execute the inner SELECT I get this:
>
> 53
> 29
> 46
> 46
> 51
> 52
> 53
> 29
> 46
> 47
> 48
> 48
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 49
> 50
> 49
> 49
> 46
> 46
> 46
> 46
> 43
> 43
> 43
> 43
> 43
> 4
> (Ignore that the last entry is 4, it's a copy-paste error and should be 43 as well. Anyway.....)
>
>
> The order is correct. Now from the outer SELECT I would expect then to get:
> 53
> 29
> 46
> 51
> 52
> 53
> 46
> .
> .
> .
> .
> 43
>
> But this is not the case. 43 is the id of the only record with messagekind 'AM' where all other have messagekind 'PD'. Yet the order in which the full query returns the results is:
> 29
> 43
> 46
> 47
> .
> .
> .
>
> Which is wrong. I can't figure out why this is wrong, but from toying around I found that depending on wether I use DISTINCT msgid or DISTINCT ON (msgid) msgid I get different results. Still, both results are wrong.
>
> The 'must sort by what you distinct on' behavior gives me a total headache. What does one have to do to sort by a criterion and at the same time not use that criterion in the distinct clause?
>
> Thank you
> A. Reichtadt
>