Re: Picking the first of an order in an aggregate query

On 12/31/12, François Beausoleil <francois(at)teksol(dot)info> wrote:
>
> Le 2012-12-31 à 15:38, Robert James a écrit :
>
>> DISTINCT is a very simple solution!
>> But I have one problem: In addition to the FIRST fields, I also do
>> want some aggregate functions. More accurately, it would be:
>>
>> SELECT grouping_field, FIRST(field_a), FIRST(field_b), SUM(field_x),
>> MAX(field_y)
>> ...
>>
>> How should I do that? Should I do two queries with a join on the
>> grouping field? Or is there a more direct way?
>
> WINDOW functions can help you:
>
> SELECT
> grouping_field
> , first_value(field_a) OVER (ORDER BY ...)
> , first_value(field_b) OVER (ORDER BY ...)
> , sum(field_x) OVER ()
> , max(field_y) OVER ()
> FROM ...
>
> The empty OVER clauses will make the sum / max work over the full result
> set, and not a subset. I really recommend reading the window functions
> section on the site.
>

I see. Will the optimizer know enough to not repeat the work for each
first_value I do? Or am I better off using a JOIN of some sort?