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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
