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

Bye!
François

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