Alexander Reichstadt <lxr@xxxxxxx> wrote: > 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 > [does not return results in the order of in subquery] > Which is wrong. No, your query is underspecified. The subquery in the FROM clause is an input relation to the top-level query, which then does a form of aggregation on that. There are no guarantees that a SELECT statement will return values in an order based on any of its input relations, unless you specify it that way. All the top-level SELECT sees is a list of msgid values, and you are asking it to eliminate duplicates to return a set of distinct values. It is doing exactly that in the way which it estimates will be the fastest. If you actually want something different from that, you must specify it. I'm not any more sure than the planner what it is that you *really* want, but maybe this?: SELECT msgid FROM ( SELECT distinct messagekind, refid_messages as msgid FROM messagehistorywithcontent WHERE (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' ')) LIKE '%gg%') ) as foo ORDER BY messagekind DESC, msgid; If that doesn't give you what you're after, we need a better explanation of what you want. A self-contained test case, where you create a table and load it with data and show a query, would be best. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
