[Dbix-class] Using 'DISTINCT ON' clause in select statement

[Paul Henrich]

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060818/24fead82/attachment.htm