Re: Problem with ORDER BY and DISTINCT ON

Steve Midgley <public(at)misuse(dot)org> writes:
> SELECT DISTINCT ON
> ("property"."state",
> CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2
> END,"search_rate_max",
> CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2
> END,"search_rate_max",
> property.id)
> property.id
> FROM property WHERE (("property"."data_priority_code" IS NOT NULL))
> ORDER BY
> "property"."state",
> CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2
> END,"search_rate_max",
> CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2
> END,"search_rate_max",
> property.id
> LIMIT 10 OFFSET 0

> RESULTS: ERROR: SELECT DISTINCT ON expressions must match initial ORDER
> BY expressions

Interesting. You realize of course that sorting by the same expression
twice is completely redundant? I haven't dug through the code yet but
I think what is happening is that ORDER BY knows that and gets rid of
the duplicate entries while DISTINCT ON fails to do so. Or some story
approximately like that. It should be fixed, but the immediate
workaround is just to get rid of the redundant sort keys:

SELECT DISTINCT ON
("property"."state",
CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2 END,
"search_rate_max",
-- CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2 END,
-- "search_rate_max",
property.id)
property.id
FROM property WHERE (("property"."data_priority_code" IS NOT NULL))
ORDER BY
"property"."state",
CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2 END,
"search_rate_max",
-- CASE WHEN ("search_rate_max" IS NOT NULL) THEN 1 ELSE 2 END,
-- "search_rate_max",
property.id
LIMIT 10 OFFSET 0

BTW, why are you bothering with the CASEs at all? Null values of
search_rate_max would sort high already.

regards, tom lane