Re: DISTINCT ON changes sort order
От | Tom Lane |
---|---|
Тема | Re: DISTINCT ON changes sort order |
Дата | |
Msg-id | 12846.1366863530@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: DISTINCT ON changes sort order (Alexander Reichstadt <info@apfeltaste.net>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: