Re: [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 <[EMAIL PROTECTED]> 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

_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/