Discussion of DBIx::Class, a perl Object-Relational Mapping module. ()

Paul Steinkamp | 18 Aug 2006 21:49

Using 'DISTINCT ON' clause in select statement

Greetings,

I'm using postgres as my DB which will accept the clause:

	SELECT DISTINCT ON ( field1, ..., fieldn)    fieldA, fieldB,...  
FROM ....

Note there is no comma after the closing ')' after the 'DISTINCT ON'  
clause. My various attempts either end up not getting it included at  
all or included as a function (and therefore separated from the  
selection field lists by a comma).

Is there a way within the class (short of raw SQL) to perform this  
kind of select?

Thanks,
Paul

headers

Brandon Black | 18 Aug 2006 21:55

Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06, Paul Steinkamp <steinkam <at> apple.com> wrote:

Greetings,

I'm using postgres as my DB which will accept the clause:

SELECT DISTINCT ON ( field1, ..., fieldn) fieldA, fieldB,...
FROM ....

Note there is no comma after the closing ')' after the 'DISTINCT ON'
clause. My various attempts either end up not getting it included at
all or included as a function (and therefore separated from the
selection field lists by a comma).

Is there a way within the class (short of raw SQL) to perform this
kind of select?

Can't the same effect be achieved with GROUP BY?

-- Brandon

<div>
<br><br><div>
<span class="gmail_quote">On 8/18/06, Paul Steinkamp &lt;<a href="mailto:steinkam <at> apple.com">steinkam <at> apple.com</a>&gt; wrote:</span><blockquote class="gmail_quote">
Greetings,<br><br>I'm using postgres as my DB which will accept the clause:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT DISTINCT ON ( field1, ..., fieldn)&nbsp;&nbsp;&nbsp;&nbsp;fieldA, fieldB,...<br>FROM ....<br><br>Note there is no comma after the closing ')' after the 'DISTINCT ON'
<br>clause. My various attempts either end up not getting it included at<br>all or included as a function (and therefore separated from the<br>selection field lists by a comma).<br><br>Is there a way within the class (short of raw SQL) to perform this
<br>kind of select?</blockquote>
<br>Can't the same effect be achieved with GROUP BY?<br><br>-- Brandon<br>
</div>
</div>

headers

Stephan Szabo | 18 Aug 2006 22:44

Re: Using 'DISTINCT ON' clause in select statement

On Fri, 18 Aug 2006, Brandon Black wrote:

> On 8/18/06, Paul Steinkamp <steinkam <at> apple.com> wrote:
> >
> > Greetings,
> >
> > I'm using postgres as my DB which will accept the clause:
> >
> >         SELECT DISTINCT ON ( field1, ..., fieldn)    fieldA, fieldB,...
> > FROM ....
> >
> > Note there is no comma after the closing ')' after the 'DISTINCT ON'
> > clause. My various attempts either end up not getting it included at
> > all or included as a function (and therefore separated from the
> > selection field lists by a comma).
> >
> > Is there a way within the class (short of raw SQL) to perform this
> > kind of select?
>
>
> Can't the same effect be achieved with GROUP BY?

Not generally if fieldA, fieldB, etc aren't in the grouping (or in this
case distincted) columns.

If I remember correctly, if the grouping columns are a key (or a superset
thereof), then SQL (as of SQL99) allows you to get direct column
references that aren't in the grouping columns or a set function, but
otherwise you cannot, presumably because there isn't a single value
guaranteed for the group. PostgreSQL doesn't yet implement the first part
where you can use only the key columns.

headers

Paul Henrich | 18 Aug 2006 22:13

Re: Using 'DISTINCT ON' clause in select statement

On Aug 18, 2006, at 3:55 PM, Brandon Black wrote:

On 8/18/06, Paul Steinkamp <steinkam <at> apple.com> wrote:
Greetings,

I'm using postgres as my DB which will accept the clause:

SELECT DISTINCT ON ( field1, ..., fieldn) fieldA, fieldB,...
FROM ....

Is there a way within the class (short of raw SQL) to perform this
kind of select?

Can't the same effect be achieved with GROUP BY?

-- Brandon

I just ran into this myself.

It seems postgres has a strange syntax/behavior for GROUP BY that becomes difficult to work with when using SQL::Abstract and when working with joined tables. Basically, any column you select must be used in the GROUP BY or in an aggregate function. In my case I was (fortunately) able to simplify things by just by setting distinct => 1.

Various attempts at using GROUP BY in my case resulted in certain columns not being returned. I am sure it is /possible/ to use GROUP BY in this situation, but it seems unnecessarily difficult with PostgreSQL.

--Paul Henrich

<div>
<div>
<div>On Aug 18, 2006, at 3:55 PM, Brandon Black wrote:</div>
<blockquote type="cite">
<br><div>
<span class="gmail_quote">On 8/18/06, Paul Steinkamp &lt;<a href="mailto:steinkam <at> apple.com">steinkam <at> apple.com</a>&gt; wrote:</span><blockquote class="gmail_quote"> Greetings,<br><br>I'm using postgres as my DB which will accept the clause:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT DISTINCT ON ( field1, ..., fieldn)&nbsp;&nbsp;&nbsp;&nbsp;fieldA, fieldB,...<br>FROM ....<br><br>Is there a way within the class (short of raw SQL) to perform this <br>kind of select?</blockquote>
<br>Can't the same effect be achieved with GROUP BY?<br><br>-- Brandon</div> </blockquote>
<div><br class="khtml-block-placeholder"></div>
<div>I just ran into this myself.</div>
<div><br class="khtml-block-placeholder"></div>
<div>It seems postgres has a strange syntax/behavior for GROUP BY that becomes difficult to work with when using SQL::Abstract and when working with joined tables. Basically, any column you select must be used in the GROUP BY or in an aggregate function. In my case I was (fortunately) able to simplify things by just by setting distinct =&gt; 1.</div>
<div><br class="khtml-block-placeholder"></div>
<div>Various attempts at using GROUP BY in my case resulted in certain columns not being returned. I am sure it is /possible/ to use GROUP BY in this situation, but it seems unnecessarily difficult with PostgreSQL.</div>
<div><br class="khtml-block-placeholder"></div>
<div>--Paul Henrich</div>
<br>
</div>
<br>
</div>

headers

Jess Robinson | 19 Aug 2006 11:50

Re: Using 'DISTINCT ON' clause in select statement


On Fri, 18 Aug 2006, Paul Henrich wrote:

> On Aug 18, 2006, at 3:55 PM, Brandon Black wrote:
>> 
>> On 8/18/06, Paul Steinkamp <steinkam <at> apple.com> wrote:
>> Greetings,
>> 
>> Can't the same effect be achieved with GROUP BY?
>> 
>> -- Brandon
>
> I just ran into this myself.
>
> It seems postgres has a strange syntax/behavior for GROUP BY that becomes 
> difficult to work with when using SQL::Abstract and when working with joined 
> tables. Basically, any column you select must be used in the GROUP BY or in 
> an aggregate function. In my case I was (fortunately) able to simplify things 
> by just by setting distinct => 1.

Strange behaviour? That's quite normal and sane..

/me curses DBs that teach people non-ansi rubbish.

Jess

headers

Mark Blythe | 18 Aug 2006 22:30

Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06, Paul Henrich <phenrich <at> butler.edu> wrote:

> It seems postgres has a strange syntax/behavior for GROUP BY that becomes
> difficult to work with when using SQL::Abstract and when working with joined
> tables. Basically, any column you select must be used in the GROUP BY or in
> an aggregate function.

If I'm not mistaken, that is ANSI SQL syntax.  MySQL allows you to
select other columns without using them in the GROUP BY, but that's an
extension of the standard.

headers

John Siracusa | 18 Aug 2006 22:43

Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06, Mark Blythe <list <at> markblythe.com> wrote:
> On 8/18/06, Paul Henrich <phenrich <at> butler.edu> wrote:
>> It seems postgres has a strange syntax/behavior for GROUP BY that becomes
>> difficult to work with when using SQL::Abstract and when working with joined
>> tables. Basically, any column you select must be used in the GROUP BY or in
>> an aggregate function.
>
> If I'm not mistaken, that is ANSI SQL syntax.  MySQL allows you to
> select other columns without using them in the GROUP BY, but that's an
> extension of the standard.

...where "extension of the standard" here means "crazy thing that
makes no sense."

    CREATE TABLE t (a INT, b INT, c INT);

    INSERT INTO t VALUES (1, 2, 3);
    INSERT INTO t VALUES (1, 2, 4);
    INSERT INTO t VALUES (1, 2, 5);
    INSERT INTO t VALUES (1, 2, 6);

Now guess what value c will have in the row returned by this query:

    SELECT a, SUM(b), c FROM t GROUP BY a;

Good old MySQL...

-John

headers

Mark Blythe | 18 Aug 2006 22:59

Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06, John Siracusa <siracusa <at> mindspring.com> wrote:

> ...where "extension of the standard" here means "crazy thing that
> makes no sense."
>
>     CREATE TABLE t (a INT, b INT, c INT);
>
>     INSERT INTO t VALUES (1, 2, 3);
>     INSERT INTO t VALUES (1, 2, 4);
>     INSERT INTO t VALUES (1, 2, 5);
>     INSERT INTO t VALUES (1, 2, 6);
>
> Now guess what value c will have in the row returned by this query:
>
>     SELECT a, SUM(b), c FROM t GROUP BY a;
>
> Good old MySQL...

Sure, the columns you select must have unique values per group.  That
limitation used to be stated in the MySQL docs.  It may still be, but
I can't find it now.  Within that limitation, the extension can be
handy in some circumstances, but as with any non-standard feature,
you're increasing the pain you'll feel if you migrate to another DB in
the future.

headers

John Siracusa | 19 Aug 2006 00:32

Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06 4:59 PM, Mark Blythe wrote:
> On 8/18/06, John Siracusa <siracusa <at> mindspring.com> wrote:
>> Now guess what value c will have in the row returned by this query:
>> 
>>     SELECT a, SUM(b), c FROM t GROUP BY a;
>> 
>> Good old MySQL...
> 
> Sure, the columns you select must have unique values per group.  That
> limitation used to be stated in the MySQL docs.

Now if only that requirement could be enforced somehow...maybe by, oh, I
don't know, the database software or something.

-John

headers

Mark Blythe | 19 Aug 2006 00:54

Re: Using 'DISTINCT ON' clause in select statement

On 8/18/06, John Siracusa <siracusa <at> mindspring.com> wrote:

> > Sure, the columns you select must have unique values per group.  That
> > limitation used to be stated in the MySQL docs.
>
> Now if only that requirement could be enforced somehow...maybe by, oh, I
> don't know, the database software or something.

Agreed.  It would definitely be helpful if it'd throw an error in that case.