Re: Problem with ORDER BY and DISTINCT ON

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Steve Midgley <public(at)misuse(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Problem with ORDER BY and DISTINCT ON
Date: 2008-07-16 14:29:30
Message-ID: 7796.1216218570@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Kaare Rasmussen 2008-07-16 16:10:10 Re: Rollback in Postgres
Previous Message Ivan Sergio Borgonovo 2008-07-16 09:06:11 integrity check and visibility was: COPY equivalent for updates