| 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:
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 |
