Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group