Thread: DISTINCT ON changes sort order on its own it seems
Hi,
(Ignore that the last entry is 4, it's a copy-paste error and should be 43 as well. Anyway.....)
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 |
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
Alexander Reichstadt <info@apfeltaste.net> writes:
> 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)
asfoo
> This query rearranges the sort order though.
It's perfectly within its rights to emit the rows in any order
whatsoever, because you wrote no ORDER BY in the outer query level.
The ORDER BY in the sub-select is not binding on the outer query.
regards, tom lane