On 12/31/12, François Beausoleil <francois@xxxxxxxxxxx> 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
