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

Re: Problem with ORDER BY and DISTINCT ON

From: Steve Midgley <public(at)misuse(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Problem with ORDER BY and DISTINCT ON
Date: 2008-07-16 17:03:42
Message-ID: 20080716170350.AA42D64FCD7@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
At 07:29 AM 7/16/2008, Tom Lane wrote:
>Steve Midgley <public(at)misuse(dot)org> writes:
>Interesting.  You realize of course that sorting by the same 
>expression
>twice is completely redundant?  I haven't dug through the code yet but
Thanks Tom. Yeah, I was a little embarrassed to throw this code up on 
the list b/c it's pretty weak. It's basically machine written - so 
sometimes the CASE features a different "THEN X ELSE Y" order.

>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:
I don't know if this will help track down the problem, but I figured 
out last night that ORDER BY is totally fine with having TWO duplicate 
entries, so long as I only put ONE entry in the DISTINCT ON area.

Of course removing the duplicate from both areas is the correct 
solution and I broke down and hacked that into the auto-sql-writing 
code and so my immediate problem is solved. I'm happy to file this as a 
ticket for Pg (please point me to your ticket tool as I've never used 
it). This is not a very big deal but Pg has such a high compliance with 
wacky-but-valid SQL it does seem like it should be fixed just because. 
Let me know if I can help on that.

Best,

Steve


In response to

Responses

pgsql-sql by date

Next:From: Volkan YAZICIDate: 2008-07-16 20:20:20
Subject: pg_advisory_lock(bigint) vs. LOCK TABLE
Previous:From: Mark RobertsDate: 2008-07-16 16:29:47
Subject: Re: How to GROUP results BY month

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