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(at)postgresql(dot)org [mailto:pgsql-general-
>owner(at)postgresql(dot)org] Im Auftrag von Manfred Koizar
>Gesendet: Donnerstag, 06. März 2003 11:39
>An: Peter Alberer
>Cc: pgsql-general(at)postgresql(dot)org
>Betreff: Re: [GENERAL] Difficult query
>
>On Wed, 5 Mar 2003 17:02:42 +0100, "Peter Alberer"
><h9351252(at)obelix(dot)wu-wien(dot)ac(dot)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