DISTINCT ON changes sort order

From: Alexander Reichstadt <lxr(at)mac(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: DISTINCT ON changes sort order
Date: 2013-04-24 14:03:09
Message-ID: 468F250C-0857-4C7E-9922-F2D51A9DB50D@mac.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shaun Thomas 2013-04-24 14:26:23 Re: DISTINCT ON changes sort order
Previous Message Alexander Reichstadt 2013-04-24 13:39:29 DISTINCT ON changes sort order on its own it seems