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 >
