Re: Difficult query

Thanks a lot Manfred that is exactly what is was searching for!

I tried a few things with distinct and max in a similar select -
sub-select combination but did not know about "distinct on".

peter

>-----Ursprüngliche Nachricht-----
>Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>owner@postgresql.org] Im Auftrag von Manfred Koizar
>Gesendet: Donnerstag, 06. März 2003 11:39
>An: Peter Alberer
>Cc: pgsql-general@postgresql.org
>Betreff: Re: [GENERAL] Difficult query
>
>On Wed, 5 Mar 2003 17:02:42 +0100, "Peter Alberer"
><h9351252@obelix.wu-wien.ac.at> wrote:
>>How can I filter the output to only return the top values per area? I
>>would prefer not to use stored-procedures or client-side code if that
is
>>not necessary.
>
>Peter,
>
>DISTINCT ON is your friend.  I never tried to use it together with
>GROUP BY;  at least you can use your original query as a sub-select
>and wrap the DISTINCT ON query around it.
>
>    SELECT DISTINCT ON (area) area, user_id, total, succ
>      FROM (SELECT ...) AS t
>     ORDER BY area, succ DESC, total DESC;
>
>Servus
> Manfred
>
>---------------------------(end of
broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html