Thread: Re: [SQL] DISTINCT ON: speak now or forever hold your peace

Julian Scarfe <jscarfe@callnetuk.com> writes:
>>> I use 'distinct on' to get the most recent reports for each of a group of
>>> locations.  E.g.:
>>> 
>>> select distinct on location * from reports where location ~~ 'Lond%'
>>> order by location, reporttime desc;
>>> 
>>> to get the tuples that offer the most recent reports for each of London,
>>> Londonderry, Londy etc.
>>> 
>>> Is there an alternative?
>> 
>> select location, max(report_time) from reports group by location

> But I want report_text *too* -- that's the important bit, and it's not an
> aggregate or common column in the group.

Right.  That is the motivation that was given for keeping DISTINCT ON
and trying to clean it up: if you ORDER BY the DISTINCT field plus
enough other fields to give a unique ordering, and then rely on the
(undocumented) implementation property that you'll get the first tuple
out of each group with the same DISTINCT field, then you can get useful
effects like this one.

It's a kluge, and it's not readily extensible to cases such as needing to
apply the DISTINCT filter across two fields, but it does solve problems
that you can't straightforwardly rewrite into GROUP BY.

Tell you what: there are two things that are really bugging me about
DISTINCT ON.  If I could get consensus on patching these things,
I'd be willing to do that rather than yank it.

One gripe is the lame-brain syntax: because there is no separator
between the DISTINCT ON column name and the first select targetlist
item, the DISTINCT ON target cannot be anything more complex than an
unadorned column name.  For example "SELECT DISTINCT ON A+B+C, ..."
must be interpreted as DISTINCT ON A with a first target of +B+C;
if we tried to allow more complex expressions then it becomes ambiguous
whether that was meant or DISTINCT ON A+B with a first target of +C.
To fix this we need some kind of syntactic separator.  The cleanest
idea that comes to my mind is to require parentheses around the ON
target:    SELECT DISTINCT ON (expression) target1, ...
One immediate advantage of allowing an expression is that you can do
distinct-on-two-fields in a rather klugy way, eg    SELECT DISTINCT ON (field1 || ' ' || field2) ...
We might someday extend it to allow multiple DISTINCT fields, eg,    SELECT DISTINCT ON (expr1 [, expr2 ...]) target1,
...
but I'm not promising to do that now (unless it's really easy ;-)).

The other gripe is the bad interaction with ORDER BY, as was illustrated
in my example of last night: if you ORDER BY something that's not the
DISTINCT field, it doesn't work.  We could get something that kind of
works by applying two sorting passes: first sort by the DISTINCT field,
then run the duplicate-elimination filter, then re-sort by the ORDER BY
field(s).  The trouble with that is that it means the user no longer has
control of the sort order within the same-DISTINCT-field-value groups
during the first sort phase, so there's no way for him to control which
tuple gets kept from each group.  What I'd prefer to do is put in an
error check that says "if you use both DISTINCT ON and ORDER BY, then
the DISTINCT ON expression must be the first ORDER BY item".  That way
the ORDER BY ordering can be the same one used for the
duplicate-elimination pass, and we don't have the semantic problem.
Note that Julian's example meets this constraint.

(BTW, ordinary SELECT DISTINCT has this same sort of problem if you try
to ORDER BY an expression that doesn't appear in the target list.
SQL92 avoids the issue by not allowing you to ORDER BY expressions that
aren't in the target list, period.  We do allow that --- but not when
you use DISTINCT.  Essentially, I want to enforce that same restriction
for DISTINCT ON.)

The other piece of the puzzle would be to document that DISTINCT ON
keeps the first tuple out of each set with the same DISTINCT ON value.

Does that sound like a plan?
        regards, tom lane

> (BTW, ordinary SELECT DISTINCT has this same sort of problem if you try
> to ORDER BY an expression that doesn't appear in the target list.
> SQL92 avoids the issue by not allowing you to ORDER BY expressions that
> aren't in the target list, period.  We do allow that --- but not when
> you use DISTINCT.  Essentially, I want to enforce that same restriction
> for DISTINCT ON.)
> 
> The other piece of the puzzle would be to document that DISTINCT ON
> keeps the first tuple out of each set with the same DISTINCT ON value.
> 
> Does that sound like a plan?

Yes, very clear.  Good.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Tom Lane wrote:
>      SELECT DISTINCT ON (expression) target1, ...
> One immediate advantage of allowing an expression is that you can do
> distinct-on-two-fields in a rather klugy way, eg
>                 SELECT DISTINCT ON (field1 || ' ' || field2) ...

As long as we're fixing the syntax, I'm wondering if it wouldn't be more
logical to have DISTINCT ON somewhere later in the syntax. I'm wondering
if that might also avoid the need for () as a side effect. Like this
perhaps....

SELECT x, y, z FROM foo WHERE z DISTINCT ON x, y ORDER BY x, y;

> What I'd prefer to do is put in an
> error check that says "if you use both DISTINCT ON and ORDER BY, then
> the DISTINCT ON expression must be the first ORDER BY item".  

Better, but still a little kludgy. What about a syntax that really
supports everything you want? Like maybe...

SELECT x, y, z FROM foo DISTINCT ON x, y DESC ORDER BY z ASC;

Distinct on now has a similar syntax to the order by clause. What this
means is, do the DISTINCT ON test by ordering DESC (so you get the
distinct item with the largest value of x, y), and then order the final
result by z ascending.

Unless I'm missing something that gives everybody what they want.

HANG ON, I've got a better idea.....

The other alternative is to make DISTINCT ON a bit like GROUP BY. So you
would have
something like

SELECT x, y, max(z) AS mmm FROM foo DISTINCT ON x, y ORDER BY mmm;

Like GROUP BY where you group by the non-aggregate fields in the SELECT
clause, you use DISTINCT ON, to mention the non-aggregate clauses in the
SELECT. The aggregate clause are used to select WHICH of the resulting
fields are selected for presentation. This would have the benefit of
being more general so you could select, say the city with the longest
name in each state...

SELECT state, zipcode, longest_string(cityname) FROM cities DISTINCT ON
state ORDER BY zipcode;

I don't know how far I'm stepping here from the SQL paradigm, but it
sure seems cool....

Chris.

Chris Bitmead <chris@bitmead.com> writes:
> As long as we're fixing the syntax, I'm wondering if it wouldn't be more
> logical to have DISTINCT ON somewhere later in the syntax.

Well, SELECT DISTINCT is that way because SQL92 says so.  Putting the
DISTINCT ON variant somewhere else might be logically purer, but I think
it'd be confusing.

Also, isn't the reason we have DISTINCT ON at all that it's there to
be compatible with MySQL or someone?  I figured adding parens would be
about the least-surprising variant syntax for a person used to those
other products.
        regards, tom lane

> Chris Bitmead <chris@bitmead.com> writes:
> > As long as we're fixing the syntax, I'm wondering if it wouldn't be more
> > logical to have DISTINCT ON somewhere later in the syntax.
> 
> Well, SELECT DISTINCT is that way because SQL92 says so.  Putting the
> DISTINCT ON variant somewhere else might be logically purer, but I think
> it'd be confusing.
> 
> Also, isn't the reason we have DISTINCT ON at all that it's there to
> be compatible with MySQL or someone?  I figured adding parens would be
> about the least-surprising variant syntax for a person used to those
> other products.
> 

Makes sense to me.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

I saw someone mention here about porting Ars Digita to PostgreSQL. Can
they elaborate on this? Is there a web page? When it's done will all
versions of Ars Digita work with postgres? How advanced? How long to go?

At 04:41 PM 1/27/00 +1100, Chris Bitmead wrote:
>
>I saw someone mention here about porting Ars Digita to PostgreSQL. Can
>they elaborate on this? Is there a web page? When it's done will all
>versions of Ars Digita work with postgres? How advanced? How long to go?

Check out http://dsl-dhogaza.pacifier.net:2000 for a snapshot of the
work-in-progress.  Feel free to poke around and play.  Much of it is
there.

That's my box, it will be moving to a larger box over the next 
few weeks, enough funding for that popped up, apparently.

That version is based on ACS 2.4, mostly ported by me.  Ben Adida
(an early arsDigita employee now on his own), me and a few others
plan to move what we've got to ACS3 over the next few days.

We also will be moving to the open source version of AOLserver,
which is now in beta (they're using a postgres-like definition
of beta, it appears to be very stable).

We really need some of the upcoming V7.0 features, like "group
by" that doesn't always return a row, avg(numeric), and other
bug fixes.  We'll love having referential integrity, too.  And
>8KB query returns will be great, means we can define all text
columns as "text" and not worry about selects dying if we select
more than one column.  We're REALLY eager for TOAST and outer
joins :)

So, target is:

Linux RH6.1
AOLserver 3.0b beta
PG V7.0 beta
ACS 3.1

By mid-February.  

We've been using sourceforge but they're quickly getting overloaded,
bogged down, and maybe just a teensy bit flaky.  We plan to move to
start using the software management features of the ACS + CVS + other
stuff (later to be moved to the ACS) and move to our own box as soon
as we get things up and running.

I've manhandled the AOLserver postgres driver, too, and it's now
considerably more robust.  It will be distributed with whatever
comes after AOLserver 3.0b beta.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

I wrote:
> To fix this we need some kind of syntactic separator.  The cleanest
> idea that comes to my mind is to require parentheses around the ON
> target:
>         SELECT DISTINCT ON (expression) target1, ...
> One immediate advantage of allowing an expression is that you can do
> distinct-on-two-fields in a rather klugy way, eg
>         SELECT DISTINCT ON (field1 || ' ' || field2) ...
> We might someday extend it to allow multiple DISTINCT fields, eg,
>         SELECT DISTINCT ON (expr1 [, expr2 ...]) target1, ...
> but I'm not promising to do that now (unless it's really easy ;-)).

FYI, I have committed this change.  It did turn out to be just as easy
to support multiple DISTINCT ON expressions, so 7.0 will acceptSELECT DISTINCT ON (expr1 [, expr2 ...]) target1, ...

> What I'd prefer to do is put in an
> error check that says "if you use both DISTINCT ON and ORDER BY, then
> the DISTINCT ON expression must be the first ORDER BY item".

More generally, if you use both then the DISTINCT and ORDER lists must
match until one or the other runs out.
        regards, tom lane