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

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
readingthe window functions section on the site. 

Bye!
François

NOTE: Please do not top-post. This list is bottom post.